Changes between Version 8 and Version 9 of OtherTopics


Ignore:
Timestamp:
09/15/26 04:58:06 (12 days ago)
Author:
232012
Comment:

--

Legend:

Unmodified
Added
Removed
Modified

  • OtherTopics

    v8 v9  
    182182
    183183{{{
    184 Sort  (cost=75.74..75.74 rows=2 width=180) (actual time=0.185..0.189 rows=0 loops=1)
     184Sort  (cost=2270.51..2270.52 rows=3 width=158) (actual time=27.771..27.775 rows=3 loops=1)
    185185  Sort Key: (round(psy.daily_sales_velocity, 2)) DESC, (round(CASE WHEN (psy.daily_sales_velocity > '0'::numeric) THEN ((p.stock)::numeric / psy.daily_sales_velocity) ELSE '9999'::numeric END, 1))
    186186  Sort Method: quicksort  Memory: 25kB
    187   Buffers: shared hit=3
    188   ->  Nested Loop  (cost=56.78..75.73 rows=2 width=180) (actual time=0.163..0.166 rows=0 loops=1)
    189         Buffers: shared hit=3
    190         ->  Hash Join  (cost=56.63..75.21 rows=2 width=92) (actual time=0.162..0.165 rows=0 loops=1)
    191               Hash Cond: (p.product_id = psy.product_id)
    192               Join Filter: (CASE WHEN (psy.daily_sales_velocity > '0'::numeric) THEN ((p.stock)::numeric / psy.daily_sales_velocity) ELSE '9999'::numeric END < '30'::numeric)
    193               Rows Removed by Join Filter: 5
    194               Buffers: shared hit=3
    195               ->  Seq Scan on products p  (cost=0.00..16.80 rows=680 width=28) (actual time=0.018..0.020 rows=17 loops=1)
    196                     Buffers: shared hit=1
    197               ->  Hash  (cost=56.57..56.57 rows=5 width=72) (actual time=0.117..0.119 rows=5 loops=1)
    198                     Buckets: 1024  Batches: 1  Memory Usage: 9kB
    199                     Buffers: shared hit=2
    200                     ->  Subquery Scan on psy  (cost=56.40..56.57 rows=5 width=72) (actual time=0.084..0.093 rows=5 loops=1)
    201                           Buffers: shared hit=2
    202                           ->  GroupAggregate  (cost=56.40..56.52 rows=5 width=72) (actual time=0.083..0.091 rows=5 loops=1)
    203                                 Group Key: op.product_id
    204                                 Buffers: shared hit=2
    205                                 ->  Sort  (cost=56.40..56.42 rows=5 width=16) (actual time=0.073..0.076 rows=12 loops=1)
    206                                       Sort Key: op.product_id
    207                                       Sort Method: quicksort  Memory: 25kB
    208                                       Buffers: shared hit=2
    209                                       ->  Hash Join  (cost=34.09..56.34 rows=5 width=16) (actual time=0.050..0.059 rows=12 loops=1)
     187  Buffers: shared hit=763
     188  ->  Hash Join  (cost=2269.23..2270.49 rows=3 width=158) (actual time=27.754..27.764 rows=3 loops=1)
     189        Hash Cond: (r.release_id = p.release_id)
     190        Buffers: shared hit=763
     191        ->  Seq Scan on releases r  (cost=0.00..1.12 rows=12 width=18) (actual time=0.015..0.017 rows=12 loops=1)
     192              Buffers: shared hit=1
     193        ->  Hash  (cost=2269.19..2269.19 rows=3 width=92) (actual time=27.724..27.727 rows=3 loops=1)
     194              Buckets: 1024  Batches: 1  Memory Usage: 9kB
     195              Buffers: shared hit=762
     196              ->  Hash Join  (cost=2267.97..2269.19 rows=3 width=92) (actual time=27.713..27.724 rows=3 loops=1)
     197                    Hash Cond: (p.product_id = psy.product_id)
     198                    Join Filter: (CASE WHEN (psy.daily_sales_velocity > '0'::numeric) THEN ((p.stock)::numeric / psy.daily_sales_velocity) ELSE '9999'::numeric END < '30'::numeric)
     199                    Rows Removed by Join Filter: 4
     200                    Buffers: shared hit=762
     201                    ->  Seq Scan on products p  (cost=0.00..1.17 rows=17 width=28) (actual time=0.006..0.008 rows=17 loops=1)
     202                          Buffers: shared hit=1
     203                    ->  Hash  (cost=2267.86..2267.86 rows=9 width=72) (actual time=27.694..27.696 rows=7 loops=1)
     204                          Buckets: 1024  Batches: 1  Memory Usage: 9kB
     205                          Buffers: shared hit=761
     206                          ->  Subquery Scan on psy  (cost=2267.64..2267.86 rows=9 width=72) (actual time=27.684..27.692 rows=7 loops=1)
     207                                Buffers: shared hit=761
     208                                ->  HashAggregate  (cost=2267.64..2267.77 rows=9 width=72) (actual time=27.684..27.690 rows=7 loops=1)
     209                                      Group Key: op.product_id
     210                                      Batches: 1  Memory Usage: 24kB
     211                                      Buffers: shared hit=761
     212                                      ->  Hash Join  (cost=753.27..2252.45 rows=3038 width=16) (actual time=6.357..24.965 rows=15012 loops=1)
    210213                                            Hash Cond: (op.order_id = o.order_id)
    211                                             Buffers: shared hit=2
    212                                             ->  Seq Scan on order_products op  (cost=0.00..19.70 rows=970 width=24) (actual time=0.007..0.009 rows=21 loops=1)
    213                                                   Buffers: shared hit=1
    214                                             ->  Hash  (cost=34.01..34.01 rows=6 width=8) (actual time=0.029..0.029 rows=9 loops=1)
    215                                                   Buckets: 1024  Batches: 1  Memory Usage: 9kB
    216                                                   Buffers: shared hit=1
    217                                                   ->  Seq Scan on orders o  (cost=0.00..34.01 rows=6 width=8) (actual time=0.013..0.019 rows=9 loops=1)
     214                                            Buffers: shared hit=761
     215                                            ->  Seq Scan on order_products op  (cost=0.00..1302.21 rows=75021 width=24) (actual time=0.008..7.114 rows=75021 loops=1)
     216                                                  Buffers: shared hit=552
     217                                            ->  Hash  (cost=740.61..740.61 rows=1013 width=8) (actual time=6.341..6.342 rows=5009 loops=1)
     218                                                  Buckets: 8192 (originally 1024)  Batches: 1 (originally 1)  Memory Usage: 260kB
     219                                                  Buffers: shared hit=209
     220                                                  ->  Seq Scan on orders o  (cost=0.00..740.61 rows=1013 width=8) (actual time=0.016..5.416 rows=5009 loops=1)
    218221                                                        Filter: ((status = ANY ('{PAID,SHIPPED,DELIVERED}'::order_status_type[])) AND (purchase_date >= (CURRENT_DATE - '1 year'::interval)))
    219                                                         Rows Removed by Filter: 8
    220                                                         Buffers: shared hit=1
    221         ->  Index Scan using releases_pkey on releases r  (cost=0.15..0.24 rows=1 width=40) (never executed)
    222               Index Cond: (release_id = p.release_id)
    223 Planning Time: 0.555 ms
    224 Execution Time: 0.272 ms
     222                                                        Rows Removed by Filter: 20008
     223                                                        Buffers: shared hit=209
     224Planning:
     225  Buffers: shared hit=70 dirtied=2
     226Planning Time: 0.940 ms
     227Execution Time: 27.871 ms
    225228}}}
    226229
    … …  
    260263}}}
    261264
    262 because scanning the table and performing a hash join was estimated to be cheaper.
    263 
    264 The query was again executed 10 times and the average execution time with indexes was: **22.299 ms**
     265because scanning the table and performing a hash join was estimated to be cheaper. This is visible in the output, shown bellow:
     266
     267{{{
     268Sort  (cost=1575.04..1575.05 rows=3 width=158) (actual time=21.944..21.948 rows=3 loops=1)
     269  Sort Key: (round(psy.daily_sales_velocity, 2)) DESC, (round(CASE WHEN (psy.daily_sales_velocity > '0'::numeric) THEN ((p.stock)::numeric / psy.daily_sales_velocity) ELSE '9999'::numeric END, 1))
     270  Sort Method: quicksort  Memory: 25kB
     271  Buffers: shared hit=576
     272  ->  Hash Join  (cost=1573.75..1575.02 rows=3 width=158) (actual time=21.927..21.937 rows=3 loops=1)
     273        Hash Cond: (r.release_id = p.release_id)
     274        Buffers: shared hit=576
     275        ->  Seq Scan on releases r  (cost=0.00..1.12 rows=12 width=18) (actual time=0.014..0.016 rows=12 loops=1)
     276              Buffers: shared hit=1
     277        ->  Hash  (cost=1573.72..1573.72 rows=3 width=92) (actual time=21.899..21.902 rows=3 loops=1)
     278              Buckets: 1024  Batches: 1  Memory Usage: 9kB
     279              Buffers: shared hit=575
     280              ->  Hash Join  (cost=1572.50..1573.72 rows=3 width=92) (actual time=21.888..21.899 rows=3 loops=1)
     281                    Hash Cond: (p.product_id = psy.product_id)
     282                    Join Filter: (CASE WHEN (psy.daily_sales_velocity > '0'::numeric) THEN ((p.stock)::numeric / psy.daily_sales_velocity) ELSE '9999'::numeric END < '30'::numeric)
     283                    Rows Removed by Join Filter: 4
     284                    Buffers: shared hit=575
     285                    ->  Seq Scan on products p  (cost=0.00..1.17 rows=17 width=28) (actual time=0.007..0.008 rows=17 loops=1)
     286                          Buffers: shared hit=1
     287                    ->  Hash  (cost=1572.39..1572.39 rows=9 width=72) (actual time=21.870..21.873 rows=7 loops=1)
     288                          Buckets: 1024  Batches: 1  Memory Usage: 9kB
     289                          Buffers: shared hit=574
     290                          ->  Subquery Scan on psy  (cost=1572.16..1572.39 rows=9 width=72) (actual time=21.861..21.869 rows=7 loops=1)
     291                                Buffers: shared hit=574
     292                                ->  HashAggregate  (cost=1572.16..1572.30 rows=9 width=72) (actual time=21.861..21.866 rows=7 loops=1)
     293                                      Group Key: op.product_id
     294                                      Batches: 1  Memory Usage: 24kB
     295                                      Buffers: shared hit=574
     296                                      ->  Hash Join  (cost=57.80..1556.97 rows=3038 width=16) (actual time=1.810..19.144 rows=15012 loops=1)
     297                                            Hash Cond: (op.order_id = o.order_id)
     298                                            Buffers: shared hit=574
     299                                            ->  Seq Scan on order_products op  (cost=0.00..1302.21 rows=75021 width=24) (actual time=0.010..5.596 rows=75021 loops=1)
     300                                                  Buffers: shared hit=552
     301                                            ->  Hash  (cost=45.14..45.14 rows=1013 width=8) (actual time=1.790..1.791 rows=5009 loops=1)
     302                                                  Buckets: 8192 (originally 1024)  Batches: 1 (originally 1)  Memory Usage: 260kB
     303                                                  Buffers: shared hit=22
     304                                                  ->  Index Only Scan using idx_orders_status_purchase_date on orders o  (cost=0.29..45.14 rows=1013 width=8) (actual time=0.040..0.939 rows=5009 loops=1)
     305                                                        Index Cond: ((status = ANY ('{PAID,SHIPPED,DELIVERED}'::order_status_type[])) AND (purchase_date >= (CURRENT_DATE - '1 year'::interval)))
     306                                                        Heap Fetches: 0
     307                                                        Buffers: shared hit=22
     308Planning:
     309  Buffers: shared hit=19
     310Planning Time: 0.712 ms
     311Execution Time: 22.040 ms
     312}}}
     313
     314The query was again executed 10 times with each execution time shown bellow:
     315
     316{{{
     317Execution 1: 22.096 ms
     318
     319Execution 2: 21.997 ms
     320
     321Execution 3: 22.138 ms
     322
     323Execution 4: 22.185 ms
     324
     325Execution 5: 23.046 ms
     326
     327Execution 6: 22.113 ms
     328
     329Execution 7: 22.039 ms
     330
     331Execution 8: 22.084 ms
     332
     333Execution 9: 23.219 ms
     334
     335Execution 10: 22.068 ms
     336}}}
     337
     338The average execution time with indexes was: **22.299 ms**
    265339
    266340==== Performance comparison and conclusion