explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ni5O

Settings
# exclusive inclusive rows x rows loops node
1. 700.134 100,844.601 ↓ 2.7 389,760 1

WindowAgg (cost=14,628,114.62..14,630,335.91 rows=143,309 width=195) (actual time=100,102.627..100,844.601 rows=389,760 loops=1)

2.          

CTE base_row_by_sku

3. 1,705.329 84,214.811 ↑ 1.4 82,910 1

Unique (cost=13,892,727.64..13,905,773.88 rows=116,443 width=103) (actual time=78,792.767..84,214.811 rows=82,910 loops=1)

4. 57,257.936 82,509.482 ↓ 1.2 16,261,037 1

Sort (cost=13,892,727.64..13,899,250.76 rows=13,046,242 width=103) (actual time=78,792.761..82,509.482 rows=16,261,037 loops=1)

  • Sort Key: daily_product_metrics_ongoing_totals.sku, daily_product_metrics_ongoing_totals.date DESC
  • Sort Method: external merge Disk: 1497112kB
5. 20,906.326 25,251.546 ↓ 1.2 16,261,037 1

Bitmap Heap Scan on daily_product_metrics_ongoing_totals (cost=329,610.06..13,584,351.91 rows=13,046,242 width=103) (actual time=5,187.560..25,251.546 rows=16,261,037 loops=1)

  • Recheck Cond: (((seller_id)::text = 'A3TUJE1XKIZPOF'::text) AND ((marketplace_id)::text = 'ATVPDKIKX0DER'::text) AND (date <= '2019-06-08 00:00:00'::timestamp without time zone))
  • Heap Blocks: exact=2015240
6. 4,345.220 4,345.220 ↓ 1.2 16,261,037 1

Bitmap Index Scan on dpm_ongoing_totals__sellerid__marketplaceid__date__parent_asin (cost=0.00..328,957.75 rows=13,046,242 width=0) (actual time=4,345.220..4,345.220 rows=16,261,037 loops=1)

  • Index Cond: (((seller_id)::text = 'A3TUJE1XKIZPOF'::text) AND ((marketplace_id)::text = 'ATVPDKIKX0DER'::text) AND (date <= '2019-06-08 00:00:00'::timestamp without time zone))
7. 1,158.343 100,144.467 ↓ 2.7 389,760 1

Sort (cost=722,340.73..722,412.39 rows=143,309 width=172) (actual time=100,102.570..100,144.467 rows=389,760 loops=1)

  • Sort Key: p.sku, ((date_range.date)::date)
  • Sort Method: quicksort Memory: 70188kB
8. 231.512 98,986.124 ↓ 2.7 389,760 1

Hash Left Join (cost=504,461.06..719,886.03 rows=143,309 width=172) (actual time=91,635.086..98,986.124 rows=389,760 loops=1)

  • Hash Cond: ((p.sku)::text = (base_row.sku)::text)
9. 866.967 14,477.935 ↓ 193.8 389,760 1

Nested Loop Left Join (cost=503,354.85..712,488.83 rows=2,011 width=88) (actual time=7,358.250..14,477.935 rows=389,760 loops=1)

  • Join Filter: (dpc.date = date_range.date)
  • Rows Removed by Join Filter: 3739654
10.          

CTE date_range

11. 0.081 0.081 ↑ 16.7 60 1

Function Scan on generate_series (cost=0.00..3.00 rows=1,000 width=8) (actual time=0.038..0.081 rows=60 loops=1)

12. 68.774 7,764.568 ↓ 193.8 389,760 1

Nested Loop (cost=503,351.74..705,227.29 rows=2,011 width=63) (actual time=7,358.127..7,764.568 rows=389,760 loops=1)

13. 0.194 0.194 ↓ 12.0 60 1

CTE Scan on date_range (cost=0.00..8.00 rows=5 width=8) (actual time=0.042..0.194 rows=60 loops=1)

  • Filter: (((date)::date >= '2019-06-09'::date) AND ((date)::date <= '2019-08-07'::date))
14. 34.179 7,695.600 ↓ 16.2 6,496 60

Materialize (cost=503,351.74..705,212.46 rows=402 width=55) (actual time=122.635..128.260 rows=6,496 loops=60)

15. 72.432 7,661.421 ↓ 16.2 6,496 1

Hash Join (cost=503,351.74..705,212.05 rows=402 width=55) (actual time=7,358.074..7,661.421 rows=6,496 loops=1)

  • Hash Cond: ((p.sku)::text = (daily_product_costs.sku)::text)
16. 238.652 285.183 ↓ 1.2 131,473 1

Bitmap Heap Scan on products p (cost=2,603.54..204,404.21 rows=111,147 width=55) (actual time=52.659..285.183 rows=131,473 loops=1)

  • Recheck Cond: (((seller_id)::text = 'A3TUJE1XKIZPOF'::text) AND ((marketplace_id)::text = 'ATVPDKIKX0DER'::text))
  • Filter: is_valid
  • Heap Blocks: exact=25711
17. 46.531 46.531 ↓ 1.2 131,476 1

Bitmap Index Scan on products_seller_marketplace_isvalid_sku_id_idx (cost=0.00..2,597.98 rows=111,147 width=0) (actual time=46.531..46.531 rows=131,476 loops=1)

  • Index Cond: (((seller_id)::text = 'A3TUJE1XKIZPOF'::text) AND ((marketplace_id)::text = 'ATVPDKIKX0DER'::text) AND (is_valid = true))
18. 183.678 7,303.806 ↓ 3.6 725,836 1

Hash (cost=500,047.59..500,047.59 rows=200,173 width=14) (actual time=7,303.806..7,303.806 rows=725,836 loops=1)

  • Buckets: 1048576 (originally 262144) Batches: 1 (originally 1) Memory Usage: 42148kB
19. 2,287.331 7,120.128 ↓ 3.6 725,836 1

HashAggregate (cost=499,447.08..500,047.59 rows=200,173 width=14) (actual time=6,913.376..7,120.128 rows=725,836 loops=1)

  • Group Key: (daily_product_costs.sku)::text
20. 4,832.797 4,832.797 ↓ 1.0 5,163,576 1

Seq Scan on daily_product_costs (cost=0.00..496,893.30 rows=5,107,550 width=14) (actual time=0.048..4,832.797 rows=5,163,576 loops=1)

  • Filter: ((date >= '2019-06-09'::date) AND (date <= '2019-08-07'::date))
  • Rows Removed by Filter: 21458754
21. 5,846.400 5,846.400 ↓ 10.0 10 389,760

Index Scan using daily_product_costs_pkey on daily_product_costs dpc (cost=0.11..3.61 rows=1 width=47) (actual time=0.009..0.015 rows=10 loops=389,760)

  • Index Cond: (((seller_id)::text = 'A3TUJE1XKIZPOF'::text) AND ((marketplace_id)::text = 'ATVPDKIKX0DER'::text) AND ((sku)::text = (p.sku)::text))
22. 28.525 84,276.677 ↑ 1.4 82,910 1

Hash (cost=698.66..698.66 rows=116,443 width=116) (actual time=84,276.677..84,276.677 rows=82,910 loops=1)

  • Buckets: 131072 Batches: 1 Memory Usage: 5308kB
23. 84,248.152 84,248.152 ↑ 1.4 82,910 1

CTE Scan on base_row_by_sku base_row (cost=0.00..698.66 rows=116,443 width=116) (actual time=78,792.782..84,248.152 rows=82,910 loops=1)

Planning time : 6.753 ms
Execution time : 101,104.882 ms