explain.depesz.com

PostgreSQL's explain analyze made readable

Result: AbIs

Settings
# exclusive inclusive rows x rows loops node
1. 2.277 109,997.705 ↓ 150.0 30,000 1

Limit (cost=99,800,025.60..99,800,029.60 rows=200 width=730) (actual time=109,916.323..109,997.705 rows=30,000 loops=1)

2.          

CTE visits

3. 2,560.030 30,162.299 ↑ 58.2 6,513,407 1

ProjectSet (cost=0.00..2,983,042.93 rows=378,960,000 width=43) (actual time=267.493..30,162.299 rows=6,513,407 loops=1)

4. 405.651 27,602.269 ↓ 1.0 3,819,035 1

Result (cost=0.00..1,059,820.93 rows=3,789,600 width=51) (actual time=267.483..27,602.269 rows=3,819,035 loops=1)

5. 321.453 27,196.618 ↓ 1.0 3,819,035 1

Append (cost=0.00..1,021,924.93 rows=3,789,600 width=51) (actual time=267.482..27,196.618 rows=3,819,035 loops=1)

6. 0.001 0.001 ↓ 0.0 0 1

Seq Scan on events ev (cost=0.00..0.00 rows=1 width=122) (actual time=0.001..0.001 rows=0 loops=1)

  • Filter: ((customer_id IS NOT NULL) AND (dt_occurred >= '2020-05-11 00:00:00+00'::timestamp with time zone) AND (dt_occurred < '2020-06-22 00:00:00+00'::timestamp with time zone) AND (account_id = 992) AND ((event_type)::text = 'visit'::text))
7. 26,633.543 26,875.164 ↓ 1.0 3,819,035 1

Bitmap Heap Scan on events_visits ev_1 (cost=110,109.95..1,021,924.93 rows=3,789,599 width=51) (actual time=267.480..26,875.164 rows=3,819,035 loops=1)

  • Recheck Cond: ((account_id = 992) AND (dt_occurred >= '2020-05-11 00:00:00+00'::timestamp with time zone) AND (dt_occurred < '2020-06-22 00:00:00+00'::timestamp with time zone))
  • Filter: ((customer_id IS NOT NULL) AND ((event_type)::text = 'visit'::text))
  • Heap Blocks: exact=139,478
8. 241.621 241.621 ↓ 1.0 3,819,035 1

Bitmap Index Scan on idx_events_visits_account_dt_occurred (cost=0.00..109,162.55 rows=3,789,599 width=0) (actual time=241.621..241.621 rows=3,819,035 loops=1)

  • Index Cond: ((account_id = 992) AND (dt_occurred >= '2020-05-11 00:00:00+00'::timestamp with time zone) AND (dt_occurred < '2020-06-22 00:00:00+00'::timestamp with time zone))
9.          

CTE visits_not_resulting_in_orders

10. 3,082.727 64,133.293 ↑ 52.8 5,384,102 1

Hash Anti Join (cost=1,818,852.67..70,102,707.67 rows=284,220,000 width=122) (actual time=28,299.964..64,133.293 rows=5,384,102 loops=1)

  • Hash Cond: ((visits.customer_id = eo.customer_id) AND (visits.their_product_id = (eo.product_id)::text))
11. 33,026.783 33,026.783 ↑ 58.2 6,513,407 1

CTE Scan on visits (cost=0.00..7,579,200.00 rows=378,960,000 width=122) (actual time=267.499..33,026.783 rows=6,513,407 loops=1)

12. 374.284 28,023.783 ↑ 1.0 1,212,973 1

Hash (cost=1,799,923.30..1,799,923.30 rows=1,261,958 width=10) (actual time=28,023.783..28,023.783 rows=1,212,973 loops=1)

  • Buckets: 2,097,152 Batches: 1 Memory Usage: 67,313kB
13. 85.373 27,649.499 ↑ 1.0 1,212,973 1

Append (cost=0.00..1,799,923.30 rows=1,261,958 width=10) (actual time=0.051..27,649.499 rows=1,212,973 loops=1)

14. 0.005 0.005 ↓ 0.0 0 1

Seq Scan on events eo (cost=0.00..0.00 rows=1 width=520) (actual time=0.005..0.005 rows=0 loops=1)

  • Filter: ((dt_occurred >= '2020-05-11 00:00:00+00'::timestamp with time zone) AND (dt_occurred < '2020-06-22 00:00:00+00'::timestamp with time zone) AND (account_id = 992) AND ((event_type)::text = 'order_item'::text))
15. 27,564.121 27,564.121 ↑ 1.0 1,212,973 1

Index Scan using idx_events_orders_items_account_dt_occurred on events_orders_items eo_1 (cost=0.56..1,799,923.30 rows=1,261,957 width=10) (actual time=0.045..27,564.121 rows=1,212,973 loops=1)

  • Index Cond: ((account_id = 992) AND (dt_occurred >= '2020-05-11 00:00:00+00'::timestamp with time zone) AND (dt_occurred < '2020-06-22 00:00:00+00'::timestamp with time zone))
  • Filter: ((event_type)::text = 'order_item'::text)
16.          

CTE price_drops_for_visited_products

17. 25.081 109,990.068 ↓ 150.0 30,000 1

Unique (cost=26,713,042.01..26,714,274.99 rows=200 width=197) (actual time=109,916.321..109,990.068 rows=30,000 loops=1)

18. 14,788.895 109,964.987 ↓ 1.4 333,826 1

Sort (cost=26,713,042.01..26,713,658.50 rows=246,596 width=197) (actual time=109,916.320..109,964.987 rows=333,826 loops=1)

  • Sort Key: visits_not_resulting_in_orders.customer_id DESC, product_price_history.price DESC
  • Sort Method: external merge Disk: 645,328kB
19. 24,608.541 95,176.092 ↓ 57.9 14,265,871 1

Hash Join (cost=346,903.81..26,690,957.14 rows=246,596 width=197) (actual time=32,642.534..95,176.092 rows=14,265,871 loops=1)

  • Hash Cond: ((visits_not_resulting_in_orders.account_id = product_price_history.account_id) AND (visits_not_resulting_in_orders.their_product_id = (product_price_history.their_id)::text))
  • Join Filter: (((product_price_history.account_id = 992) AND (product_price_history.dt_updated > '2020-06-22 00:00:15.137084+00'::timestamp with time zone) AND (product_price_history.dt_updated <= '2020-06-22 02:25:30.311305+00'::timestamp with time zone) AND (floor(((('-1'::numeric * product_price_history.change) / (product_price_history.price + ('-1'::numeric * product_price_history.change))) * 100.0)) >= '15'::numeric) AND ((product_price_history.store_id)::text = (visits_not_resulting_in_orders.store_id)::text)) OR ((product_price_history.store_id IS NULL) AND products.is_active AND products.stock_is_in_stock))
  • Rows Removed by Join Filter: 963,775
20. 66,227.335 66,227.335 ↑ 52.8 5,384,102 1

CTE Scan on visits_not_resulting_in_orders (cost=0.00..5,684,400.00 rows=284,220,000 width=122) (actual time=28,299.968..66,227.335 rows=5,384,102 loops=1)

21. 484.425 4,340.216 ↓ 2.6 1,337,062 1

Hash (cost=339,308.10..339,308.10 rows=506,381 width=118) (actual time=4,340.216..4,340.216 rows=1,337,062 loops=1)

  • Buckets: 2,097,152 (originally 524288) Batches: 1 (originally 1) Memory Usage: 123,636kB
22. 672.984 3,855.791 ↓ 2.6 1,337,062 1

Hash Join (cost=196,857.90..339,308.10 rows=506,381 width=118) (actual time=3,035.549..3,855.791 rows=1,337,062 loops=1)

  • Hash Cond: ((product_price_history.product_id = products.id) AND (product_price_history.account_id = products.account_id))
23. 165.259 165.259 ↑ 1.0 1,337,062 1

Seq Scan on product_price_history (cost=0.00..135,354.54 rows=1,351,554 width=116) (actual time=0.007..165.259 rows=1,337,062 loops=1)

24. 733.295 3,017.548 ↓ 1.1 2,489,852 1

Hash (cost=162,302.16..162,302.16 rows=2,303,716 width=10) (actual time=3,017.548..3,017.548 rows=2,489,852 loops=1)

  • Buckets: 4,194,304 Batches: 1 Memory Usage: 134,890kB
25. 2,284.253 2,284.253 ↓ 1.1 2,489,852 1

Seq Scan on products (cost=0.00..162,302.16 rows=2,303,716 width=10) (actual time=0.468..2,284.253 rows=2,489,852 loops=1)

26. 109,995.428 109,995.428 ↓ 150.0 30,000 1

CTE Scan on price_drops_for_visited_products (cost=0.00..4.00 rows=200 width=730) (actual time=109,916.323..109,995.428 rows=30,000 loops=1)

Planning time : 7.948 ms
Execution time : 110,213.082 ms