Changes between Version 12 and Version 13 of OtherTopics


Ignore:
Timestamp:
09/15/26 05:33:03 (12 days ago)
Author:
232012
Comment:

--

Legend:

Unmodified
Added
Removed
Modified

  • OtherTopics

    v12 v13  
    377377== Scenario 2 - Slow Moving Products
    378378
     379==== Query Used
     380
     381{{{
     382SET search_path TO project;
     383
     384EXPLAIN (ANALYZE, BUFFERS)
     385WITH product_sales_6months AS (
     386    SELECT
     387        op.product_id,
     388        SUM(op.quantity) AS units_sold_6m,
     389        MAX(o.purchase_date) AS last_purchase_date
     390    FROM order_products op
     391    JOIN orders o
     392        ON op.order_id = o.order_id
     393    WHERE o.purchase_date >= CURRENT_DATE - INTERVAL '6 months'
     394      AND o.status IN ('PAID', 'SHIPPED', 'DELIVERED')
     395    GROUP BY op.product_id
     396),
     397product_wishlist_counts AS (
     398    SELECT
     399        product_id,
     400        COUNT(wishlist_id) AS wishlist_addition_count
     401    FROM wishlist_products
     402    GROUP BY product_id
     403)
     404SELECT
     405    p.product_id,
     406    r.title AS release_title,
     407    p.format,
     408    p.stock AS unsold_stock_quantity,
     409    p.price AS current_unit_price,
     410    (p.stock * p.price) AS frozen_capital,
     411    COALESCE(TO_CHAR(ps.last_purchase_date, 'YYYY-MM-DD'), 'NEVER BOUGHT') AS last_sold_date,
     412    COALESCE(pw.wishlist_addition_count, 0) AS times_on_wishlists,
     413    CASE
     414        WHEN COALESCE(pw.wishlist_addition_count, 0) > 0
     415            THEN 'Discount Target (Wishlisted)'
     416        ELSE 'Deep Liquidation/Clearance Target'
     417    END AS inventory_action_plan
     418FROM products p
     419JOIN releases r
     420    ON p.release_id = r.release_id
     421LEFT JOIN product_sales_6months ps
     422    ON p.product_id = ps.product_id
     423LEFT JOIN product_wishlist_counts pw
     424    ON p.product_id = pw.product_id
     425WHERE ps.product_id IS NULL
     426  AND p.stock > 0
     427ORDER BY frozen_capital DESC, times_on_wishlists DESC;
     428}}}
     429
    379430{{{#!div style="text-align: justify; width: 100%;"
    380431
    … …  
    384435
    385436{{{
    386 Seq Scan on orders
    387 rows=2549
    388 Rows Removed by Filter: 22468
     437Sort  (cost=2087.37..2087.38 rows=7 width=140) (actual time=11.602..11.606 rows=9 loops=1)
     438  Sort Key: (((p.stock)::numeric * p.price)) DESC, (COALESCE((count(wishlist_products.wishlist_id)), '0'::bigint)) DESC
     439  Sort Method: quicksort  Memory: 26kB
     440  Buffers: shared hit=5338
     441  ->  Hash Join  (cost=2082.49..2087.27 rows=7 width=140) (actual time=11.568..11.591 rows=9 loops=1)
     442        Hash Cond: (p.release_id = r.release_id)
     443        Buffers: shared hit=5338
     444        ->  Hash Anti Join  (cost=2081.22..2085.88 rows=7 width=46) (actual time=11.533..11.550 rows=9 loops=1)
     445              Hash Cond: (p.product_id = ps.product_id)
     446              Buffers: shared hit=5337
     447              ->  Hash Right Join  (cost=36.91..41.45 rows=16 width=42) (actual time=0.047..0.059 rows=16 loops=1)
     448                    Hash Cond: (wishlist_products.product_id = p.product_id)
     449                    Buffers: shared hit=2
     450                    ->  HashAggregate  (cost=35.50..37.50 rows=200 width=16) (actual time=0.022..0.026 rows=9 loops=1)
     451                          Group Key: wishlist_products.product_id
     452                          Batches: 1  Memory Usage: 40kB
     453                          Buffers: shared hit=1
     454                          ->  Seq Scan on wishlist_products  (cost=0.00..27.00 rows=1700 width=16) (actual time=0.007..0.008 rows=15 loops=1)
     455                                Buffers: shared hit=1
     456                    ->  Hash  (cost=1.21..1.21 rows=16 width=34) (actual time=0.017..0.018 rows=16 loops=1)
     457                          Buckets: 1024  Batches: 1  Memory Usage: 10kB
     458                          Buffers: shared hit=1
     459                          ->  Seq Scan on products p  (cost=0.00..1.21 rows=16 width=34) (actual time=0.009..0.013 rows=16 loops=1)
     460                                Filter: (stock > 0)
     461                                Rows Removed by Filter: 1
     462                                Buffers: shared hit=1
     463              ->  Hash  (cost=2044.19..2044.19 rows=10 width=12) (actual time=11.480..11.481 rows=7 loops=1)
     464                    Buckets: 1024  Batches: 1  Memory Usage: 9kB
     465                    Buffers: shared hit=5335
     466                    ->  Subquery Scan on ps  (cost=2043.99..2044.19 rows=10 width=12) (actual time=11.474..11.477 rows=7 loops=1)
     467                          Buffers: shared hit=5335
     468                          ->  HashAggregate  (cost=2043.99..2044.09 rows=10 width=44) (actual time=11.473..11.475 rows=7 loops=1)
     469                                Group Key: op.product_id
     470                                Batches: 1  Memory Usage: 24kB
     471                                Buffers: shared hit=5335
     472                                ->  Nested Loop  (cost=0.29..2036.24 rows=1550 width=12) (actual time=0.022..9.997 rows=7633 loops=1)
     473                                      Buffers: shared hit=5335
     474                                      ->  Seq Scan on orders o  (cost=0.00..740.61 rows=517 width=12) (actual time=0.011..4.065 rows=2549 loops=1)
     475                                            Filter: ((status = ANY ('{PAID,SHIPPED,DELIVERED}'::order_status_type[])) AND (purchase_date >= (CURRENT_DATE - '6 mons'::interval)))
     476                                            Rows Removed by Filter: 22468
     477                                            Buffers: shared hit=209
     478                                      ->  Index Only Scan using order_products_pk on order_products op  (cost=0.29..2.48 rows=3 width=16) (actual time=0.001..0.002 rows=3 loops=2549)
     479                                            Index Cond: (order_id = o.order_id)
     480                                            Heap Fetches: 67
     481                                            Buffers: shared hit=5126
     482        ->  Hash  (cost=1.12..1.12 rows=12 width=18) (actual time=0.024..0.024 rows=12 loops=1)
     483              Buckets: 1024  Batches: 1  Memory Usage: 9kB
     484              Buffers: shared hit=1
     485              ->  Seq Scan on releases r  (cost=0.00..1.12 rows=12 width=18) (actual time=0.014..0.017 rows=12 loops=1)
     486                    Buffers: shared hit=1
     487Planning:
     488  Buffers: shared hit=50 dirtied=1
     489Planning Time: 0.896 ms
     490Execution Time: 11.750 ms
    389491}}}
    390492
    … …  
    395497}}}
    396498
    397 The query was executed 10 times and the average execution time without indexes was: **12.125 ms**
     499The query was executed 10 times with each execution time shown bellow:
     500
     501{{{
     502Execution 1: 11.890 ms
     503
     504Execution 2: 11.966 ms
     505
     506Execution 3: 11.938 ms
     507
     508Execution 4: 11.937 ms
     509
     510Execution 5: 11.936 ms
     511
     512Execution 6: 11.896 ms
     513
     514Execution 7: 11.932 ms
     515
     516Execution 8: 12.307 ms
     517
     518Execution 9: 13.441 ms
     519
     520Execution 10: 12.003 ms         
     521}}}
     522
     523The average execution time without indexes was: **12.125 ms**
    398524
    399525==== Indexes
    … …  
    421547The existing {{{order_products_pk}}} index continued to be used for the join with {{{order_products}}}.
    422548
    423 The query was again executed 10 times and the average execution time with indexes was: **8.894 ms**
     549This is shown in the output bellow:
     550
     551{{{
     552Sort  (cost=1369.95..1369.97 rows=7 width=140) (actual time=8.476..8.481 rows=9 loops=1)
     553  Sort Key: (((p.stock)::numeric * p.price)) DESC, (COALESCE((count(wishlist_products.wishlist_id)), '0'::bigint)) DESC
     554  Sort Method: quicksort  Memory: 26kB
     555  Buffers: shared hit=5146
     556  ->  Hash Join  (cost=1365.08..1369.86 rows=7 width=140) (actual time=8.442..8.465 rows=9 loops=1)
     557        Hash Cond: (p.release_id = r.release_id)
     558        Buffers: shared hit=5146
     559        ->  Hash Anti Join  (cost=1363.81..1368.47 rows=7 width=46) (actual time=8.405..8.422 rows=9 loops=1)
     560              Hash Cond: (p.product_id = ps.product_id)
     561              Buffers: shared hit=5145
     562              ->  Hash Right Join  (cost=36.91..41.45 rows=16 width=42) (actual time=0.045..0.057 rows=16 loops=1)
     563                    Hash Cond: (wishlist_products.product_id = p.product_id)
     564                    Buffers: shared hit=2
     565                    ->  HashAggregate  (cost=35.50..37.50 rows=200 width=16) (actual time=0.021..0.025 rows=9 loops=1)
     566                          Group Key: wishlist_products.product_id
     567                          Batches: 1  Memory Usage: 40kB
     568                          Buffers: shared hit=1
     569                          ->  Seq Scan on wishlist_products  (cost=0.00..27.00 rows=1700 width=16) (actual time=0.007..0.008 rows=15 loops=1)
     570                                Buffers: shared hit=1
     571                    ->  Hash  (cost=1.21..1.21 rows=16 width=34) (actual time=0.016..0.017 rows=16 loops=1)
     572                          Buckets: 1024  Batches: 1  Memory Usage: 10kB
     573                          Buffers: shared hit=1
     574                          ->  Seq Scan on products p  (cost=0.00..1.21 rows=16 width=34) (actual time=0.008..0.012 rows=16 loops=1)
     575                                Filter: (stock > 0)
     576                                Rows Removed by Filter: 1
     577                                Buffers: shared hit=1
     578              ->  Hash  (cost=1326.77..1326.77 rows=10 width=12) (actual time=8.354..8.355 rows=7 loops=1)
     579                    Buckets: 1024  Batches: 1  Memory Usage: 9kB
     580                    Buffers: shared hit=5143
     581                    ->  Subquery Scan on ps  (cost=1326.57..1326.77 rows=10 width=12) (actual time=8.347..8.351 rows=7 loops=1)
     582                          Buffers: shared hit=5143
     583                          ->  HashAggregate  (cost=1326.57..1326.67 rows=10 width=44) (actual time=8.347..8.349 rows=7 loops=1)
     584                                Group Key: op.product_id
     585                                Batches: 1  Memory Usage: 24kB
     586                                Buffers: shared hit=5143
     587                                ->  Nested Loop  (cost=0.58..1318.82 rows=1550 width=12) (actual time=0.045..6.925 rows=7633 loops=1)
     588                                      Buffers: shared hit=5143
     589                                      ->  Index Only Scan using idx_orders_status_purchase_date on orders o  (cost=0.29..23.20 rows=517 width=12) (actual time=0.036..0.466 rows=2549 loops=1)
     590                                            Index Cond: ((status = ANY ('{PAID,SHIPPED,DELIVERED}'::order_status_type[])) AND (purchase_date >= (CURRENT_DATE - '6 mons'::interval)))
     591                                            Heap Fetches: 0
     592                                            Buffers: shared hit=17
     593                                      ->  Index Only Scan using order_products_pk on order_products op  (cost=0.29..2.48 rows=3 width=16) (actual time=0.002..0.002 rows=3 loops=2549)
     594                                            Index Cond: (order_id = o.order_id)
     595                                            Heap Fetches: 67
     596                                            Buffers: shared hit=5126
     597        ->  Hash  (cost=1.12..1.12 rows=12 width=18) (actual time=0.026..0.026 rows=12 loops=1)
     598              Buckets: 1024  Batches: 1  Memory Usage: 9kB
     599              Buffers: shared hit=1
     600              ->  Seq Scan on releases r  (cost=0.00..1.12 rows=12 width=18) (actual time=0.016..0.019 rows=12 loops=1)
     601                    Buffers: shared hit=1
     602Planning:
     603  Buffers: shared hit=52
     604Planning Time: 0.988 ms
     605Execution Time: 8.596 ms
     606}}}
     607
     608The query was executed 10 times with each execution time shown bellow:
     609
     610{{{
     611Execution 1: 9.484 ms
     612
     613Execution 2: 8.827 ms
     614
     615Execution 3: 8.856 ms
     616
     617Execution 4: 9.122 ms
     618
     619Execution 5: 8.683 ms
     620
     621Execution 6: 9.017 ms
     622
     623Execution 7: 8.697 ms
     624
     625Execution 8: 8.747 ms
     626
     627Execution 9: 8.830 ms
     628
     629Execution 10: 8.676 ms
     630}}}
     631
     632The average execution time with indexes was: **8.894 ms**
    424633
    425634==== Performance comparison and conclusion
    … …  
    438647== Scenario 3 - Impact of Admin Discounts on Sales Numbers
    439648
     649==== Query Used
     650
     651{{{
     652SET search_path TO project;
     653
     654EXPLAIN (ANALYZE, BUFFERS)
     655WITH discount_events AS (
     656    SELECT
     657        m.modification_id,
     658        m.admin_id,
     659        m.date_modified,
     660        m.discount AS discount_percentage,
     661        mp.product_id
     662    FROM modifications m
     663    JOIN modification_products mp
     664        ON m.modification_id = mp.modification_id
     665    WHERE m.type_of_modification = 'DISCOUNT'
     666),
     667
     668pre_promo_sales AS (
     669    SELECT
     670        de.modification_id,
     671        de.product_id,
     672
     673        COALESCE(
     674            SUM(op.quantity) FILTER (WHERE o.order_id IS NOT NULL),
     675            0
     676        ) AS units_sold_before,
     677
     678        COALESCE(
     679            SUM(op.quantity * op.price_at_purchase)
     680                FILTER (WHERE o.order_id IS NOT NULL),
     681            0.00
     682        ) AS revenue_before
     683
     684    FROM discount_events de
     685    LEFT JOIN order_products op
     686        ON de.product_id = op.product_id
     687    LEFT JOIN orders o
     688        ON op.order_id = o.order_id
     689       AND o.purchase_date >= de.date_modified - INTERVAL '30 days'
     690       AND o.purchase_date < de.date_modified
     691       AND o.status IN ('PAID', 'SHIPPED', 'DELIVERED')
     692    GROUP BY de.modification_id, de.product_id
     693),
     694
     695post_promo_sales AS (
     696    SELECT
     697        de.modification_id,
     698        de.product_id,
     699
     700        COALESCE(
     701            SUM(op.quantity) FILTER (WHERE o.order_id IS NOT NULL),
     702            0
     703        ) AS units_sold_after,
     704
     705        COALESCE(
     706            SUM(op.quantity * op.price_at_purchase)
     707                FILTER (WHERE o.order_id IS NOT NULL),
     708            0.00
     709        ) AS revenue_after
     710
     711    FROM discount_events de
     712    LEFT JOIN order_products op
     713        ON de.product_id = op.product_id
     714    LEFT JOIN orders o
     715        ON op.order_id = o.order_id
     716       AND o.purchase_date > de.date_modified
     717       AND o.purchase_date <= de.date_modified + INTERVAL '30 days'
     718       AND o.status IN ('PAID', 'SHIPPED', 'DELIVERED')
     719    GROUP BY de.modification_id, de.product_id
     720),
     721
     722promo_summary AS (
     723    SELECT
     724        de.product_id,
     725        r.title AS release_title,
     726        p.format AS product_format,
     727        de.date_modified AS promotion_start_date,
     728        de.discount_percentage AS discount_applied,
     729        pre.units_sold_before,
     730        post.units_sold_after,
     731        (post.units_sold_after - pre.units_sold_before) AS volume_change,
     732        pre.revenue_before,
     733        post.revenue_after,
     734        (post.revenue_after - pre.revenue_before) AS net_revenue_impact
     735    FROM discount_events de
     736    JOIN products p
     737        ON de.product_id = p.product_id
     738    JOIN releases r
     739        ON p.release_id = r.release_id
     740    JOIN pre_promo_sales pre
     741        ON de.modification_id = pre.modification_id
     742       AND de.product_id = pre.product_id
     743    JOIN post_promo_sales post
     744        ON de.modification_id = post.modification_id
     745       AND de.product_id = post.product_id
     746)
     747
     748SELECT
     749    product_id,
     750    release_title,
     751    product_format,
     752    promotion_start_date,
     753    discount_applied,
     754    units_sold_before,
     755    units_sold_after,
     756    volume_change,
     757    revenue_before,
     758    revenue_after,
     759    net_revenue_impact,
     760    CASE
     761        WHEN net_revenue_impact > 0
     762             AND volume_change > 0
     763            THEN 'SUCCESS: Volume generated profit'
     764        WHEN net_revenue_impact < 0
     765             AND volume_change > 0
     766            THEN 'MARGIN LOSS: Volume rose but lost overall revenue'
     767        WHEN volume_change <= 0
     768            THEN 'FAILURE: No demand increase observed'
     769        ELSE 'NEUTRAL'
     770    END AS promotion_verdict
     771FROM promo_summary
     772ORDER BY promotion_start_date DESC, net_revenue_impact DESC;
     773}}}
     774
    440775{{{#!div style="text-align: justify; width: 100%;"
    441776
    … …  
    445780
    446781{{{
    447 Seq Scan on order_products
    448 rows=75021
    449 
    450 Seq Scan on orders
    451 rows=5009
    452 Rows Removed by Filter: 20008
    453 }}}
    454 
    455 The query was executed 10 times and the average execution time without indexes was: **103.004 ms**
     782Sort  (cost=8603.00..8603.01 rows=1 width=282) (actual time=103.277..103.288 rows=6 loops=1)
     783  Sort Key: de.date_modified DESC, (((COALESCE(sum(((op_1.quantity)::numeric * op_1.price_at_purchase)) FILTER (WHERE (o_1.order_id IS NOT NULL)), 0.00)) - pre.revenue_before)) DESC
     784  Sort Method: quicksort  Memory: 25kB
     785  Buffers: shared hit=1537
     786  CTE discount_events
     787    ->  Hash Join  (cost=22.19..55.57 rows=10 width=60) (actual time=0.033..0.039 rows=6 loops=1)
     788          Hash Cond: (mp.modification_id = m.modification_id)
     789          Buffers: shared hit=2
     790          ->  Seq Scan on modification_products mp  (cost=0.00..28.50 rows=1850 width=16) (actual time=0.007..0.009 rows=16 loops=1)
     791                Buffers: shared hit=1
     792          ->  Hash  (cost=22.12..22.12 rows=5 width=52) (actual time=0.016..0.017 rows=5 loops=1)
     793                Buckets: 1024  Batches: 1  Memory Usage: 9kB
     794                Buffers: shared hit=1
     795                ->  Seq Scan on modifications m  (cost=0.00..22.12 rows=5 width=52) (actual time=0.009..0.011 rows=5 loops=1)
     796                      Filter: (type_of_modification = 'DISCOUNT'::modification_type)
     797                      Rows Removed by Filter: 8
     798                      Buffers: shared hit=1
     799  ->  Nested Loop  (cost=8545.32..8547.43 rows=1 width=282) (actual time=103.241..103.270 rows=6 loops=1)
     800        Buffers: shared hit=1537
     801        ->  Hash Join  (cost=8545.18..8546.43 rows=1 width=184) (actual time=103.196..103.209 rows=6 loops=1)
     802              Hash Cond: (p.product_id = de.product_id)
     803              Buffers: shared hit=1525
     804              ->  Seq Scan on products p  (cost=0.00..1.17 rows=17 width=20) (actual time=0.012..0.015 rows=17 loops=1)
     805                    Buffers: shared hit=1
     806              ->  Hash  (cost=8545.17..8545.17 rows=1 width=188) (actual time=103.173..103.179 rows=6 loops=1)
     807                    Buckets: 1024  Batches: 1  Memory Usage: 9kB
     808                    Buffers: shared hit=1524
     809                    ->  Nested Loop  (cost=8544.61..8545.17 rows=1 width=188) (actual time=103.116..103.167 rows=6 loops=1)
     810                          Join Filter: ((de.product_id = de_2.product_id) AND (de.modification_id = de_2.modification_id))
     811                          Rows Removed by Join Filter: 15
     812                          Buffers: shared hit=1524
     813                          ->  Merge Join  (cost=4272.70..4272.86 rows=1 width=132) (actual time=53.280..53.318 rows=6 loops=1)
     814                                Merge Cond: ((de.product_id = pre.product_id) AND (de.modification_id = pre.modification_id))
     815                                Buffers: shared hit=763
     816                                ->  Sort  (cost=0.37..0.39 rows=10 width=52) (actual time=0.050..0.052 rows=6 loops=1)
     817                                      Sort Key: de.product_id, de.modification_id
     818                                      Sort Method: quicksort  Memory: 25kB
     819                                      Buffers: shared hit=2
     820                                      ->  CTE Scan on discount_events de  (cost=0.00..0.20 rows=10 width=52) (actual time=0.036..0.043 rows=6 loops=1)
     821                                            Buffers: shared hit=2
     822                                ->  Sort  (cost=4272.33..4272.36 rows=10 width=80) (actual time=53.225..53.255 rows=6 loops=1)
     823                                      Sort Key: pre.product_id, pre.modification_id
     824                                      Sort Method: quicksort  Memory: 25kB
     825                                      Buffers: shared hit=761
     826                                      ->  Subquery Scan on pre  (cost=4271.92..4272.17 rows=10 width=80) (actual time=53.212..53.221 rows=6 loops=1)
     827                                            Buffers: shared hit=761
     828                                            ->  HashAggregate  (cost=4271.92..4272.07 rows=10 width=80) (actual time=53.211..53.218 rows=6 loops=1)
     829                                                  Group Key: de_1.modification_id, de_1.product_id
     830                                                  Batches: 1  Memory Usage: 24kB
     831                                                  Buffers: shared hit=761
     832                                                  ->  Hash Left Join  (cost=615.92..3146.60 rows=75021 width=39) (actual time=4.919..41.555 rows=50007 loops=1)
     833                                                        Hash Cond: (op.order_id = o.order_id)
     834                                                        Join Filter: ((o.purchase_date < de_1.date_modified) AND (o.purchase_date >= (de_1.date_modified - '30 days'::interval)))
     835                                                        Rows Removed by Join Filter: 9178
     836                                                        Buffers: shared hit=761
     837                                                        ->  Hash Right Join  (cost=0.33..2334.07 rows=75021 width=43) (actual time=0.023..25.566 rows=50007 loops=1)
     838                                                              Hash Cond: (op.product_id = de_1.product_id)
     839                                                              Buffers: shared hit=552
     840                                                              ->  Seq Scan on order_products op  (cost=0.00..1302.21 rows=75021 width=31) (actual time=0.011..5.656 rows=75021 loops=1)
     841                                                                    Buffers: shared hit=552
     842                                                              ->  Hash  (cost=0.20..0.20 rows=10 width=20) (actual time=0.004..0.005 rows=6 loops=1)
     843                                                                    Buckets: 1024  Batches: 1  Memory Usage: 9kB
     844                                                                    ->  CTE Scan on discount_events de_1  (cost=0.00..0.20 rows=10 width=20) (actual time=0.001..0.002 rows=6 loops=1)
     845                                                        ->  Hash  (cost=552.98..552.98 rows=5009 width=12) (actual time=4.879..4.879 rows=5009 loops=1)
     846                                                              Buckets: 8192  Batches: 1  Memory Usage: 280kB
     847                                                              Buffers: shared hit=209
     848                                                              ->  Seq Scan on orders o  (cost=0.00..552.98 rows=5009 width=12) (actual time=0.011..3.828 rows=5009 loops=1)
     849                                                                    Filter: (status = ANY ('{PAID,SHIPPED,DELIVERED}'::order_status_type[]))
     850                                                                    Rows Removed by Filter: 20008
     851                                                                    Buffers: shared hit=209
     852                          ->  HashAggregate  (cost=4271.92..4272.07 rows=10 width=80) (actual time=8.305..8.307 rows=4 loops=6)
     853                                Group Key: de_2.modification_id, de_2.product_id
     854                                Batches: 1  Memory Usage: 24kB
     855                                Buffers: shared hit=761
     856                                ->  Hash Left Join  (cost=615.92..3146.60 rows=75021 width=39) (actual time=4.801..38.509 rows=50007 loops=1)
     857                                      Hash Cond: (op_1.order_id = o_1.order_id)
     858                                      Join Filter: ((o_1.purchase_date > de_2.date_modified) AND (o_1.purchase_date <= (de_2.date_modified + '30 days'::interval)))
     859                                      Rows Removed by Join Filter: 9680
     860                                      Buffers: shared hit=761
     861                                      ->  Hash Right Join  (cost=0.33..2334.07 rows=75021 width=43) (actual time=0.045..23.831 rows=50007 loops=1)
     862                                            Hash Cond: (op_1.product_id = de_2.product_id)
     863                                            Buffers: shared hit=552
     864                                            ->  Seq Scan on order_products op_1  (cost=0.00..1302.21 rows=75021 width=31) (actual time=0.012..5.643 rows=75021 loops=1)
     865                                                  Buffers: shared hit=552
     866                                            ->  Hash  (cost=0.20..0.20 rows=10 width=20) (actual time=0.010..0.010 rows=6 loops=1)
     867                                                  Buckets: 1024  Batches: 1  Memory Usage: 9kB
     868                                                  ->  CTE Scan on discount_events de_2  (cost=0.00..0.20 rows=10 width=20) (actual time=0.001..0.003 rows=6 loops=1)
     869                                      ->  Hash  (cost=552.98..552.98 rows=5009 width=12) (actual time=4.695..4.695 rows=5009 loops=1)
     870                                            Buckets: 8192  Batches: 1  Memory Usage: 280kB
     871                                            Buffers: shared hit=209
     872                                            ->  Seq Scan on orders o_1  (cost=0.00..552.98 rows=5009 width=12) (actual time=0.009..3.563 rows=5009 loops=1)
     873                                                  Filter: (status = ANY ('{PAID,SHIPPED,DELIVERED}'::order_status_type[]))
     874                                                  Rows Removed by Filter: 20008
     875                                                  Buffers: shared hit=209
     876        ->  Index Scan using releases_pkey on releases r  (cost=0.14..0.86 rows=1 width=18) (actual time=0.007..0.007 rows=1 loops=6)
     877              Index Cond: (release_id = p.release_id)
     878              Buffers: shared hit=12
     879Planning:
     880  Buffers: shared hit=16
     881Planning Time: 1.786 ms
     882Execution Time: 103.499 ms
     883}}}
     884
     885The query was executed 10 times with each execution time shown bellow:
     886
     887{{{
     888Execution 1: 102.707 ms
     889
     890Execution 2: 103.007 ms
     891
     892Execution 3: 103.043 ms
     893
     894Execution 4: 103.082 ms
     895
     896Execution 5: 103.161 ms
     897
     898Execution 6: 102.745 ms
     899
     900Execution 7: 104.608 ms
     901
     902Execution 8: 102.366 ms
     903
     904Execution 9: 102.757 ms
     905
     906Execution 10: 102.562 ms
     907}}}
     908
     909The average execution time without indexes was: **103.004 ms**
    456910
    457911==== Indexes