explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 9toj

Settings
# exclusive inclusive rows x rows loops node
1. 93.379 35,703.438 ↓ 5.2 175,375 1

Sort (cost=697,641.53..697,658.47 rows=33,892 width=195) (actual time=35,684.016..35,703.438 rows=175,375 loops=1)

  • Sort Key: ((date_range.date)::date) DESC
  • Sort Method: quicksort Memory: 30807kB
2.          

CTE base_row_by_sku

3. 33.359 5,854.135 ↑ 12.4 4,927 1

Unique (cost=173,915.83..174,003.64 rows=61,066 width=103) (actual time=5,798.256..5,854.135 rows=4,927 loops=1)

4. 759.824 5,820.776 ↓ 3.3 289,909 1

Sort (cost=173,915.83..173,959.73 rows=87,812 width=103) (actual time=5,798.250..5,820.776 rows=289,909 loops=1)

  • Sort Key: daily_product_metrics_ongoing_totals.sku, daily_product_metrics_ongoing_totals.date DESC
  • Sort Method: quicksort Memory: 62217kB
5. 3,125.182 5,060.952 ↓ 3.3 289,909 1

Bitmap Heap Scan on daily_product_metrics_ongoing_totals (cost=2,283.97..172,473.77 rows=87,812 width=103) (actual time=1,962.314..5,060.952 rows=289,909 loops=1)

  • Recheck Cond: (((seller_id)::text = 'A1LMQBEOGTEVAC'::text) AND ((marketplace_id)::text = 'ATVPDKIKX0DER'::text) AND (date >= '2019-04-10 00:00:00'::timestamp without time zone) AND (date <= '2019-06-08 00:00:00'::timestamp without time zone))
  • Heap Blocks: exact=77494
6. 1,935.770 1,935.770 ↓ 3.3 289,909 1

Bitmap Index Scan on dpm_ongoing_totals__sellerid__marketplaceid__date__parent_asin (cost=0.00..2,279.58 rows=87,812 width=0) (actual time=1,935.770..1,935.770 rows=289,909 loops=1)

  • Index Cond: (((seller_id)::text = 'A1LMQBEOGTEVAC'::text) AND ((marketplace_id)::text = 'ATVPDKIKX0DER'::text) AND (date >= '2019-04-10 00:00:00'::timestamp without time zone) AND (date <= '2019-06-08 00:00:00'::timestamp without time zone))
7. 361.598 35,610.059 ↓ 5.2 175,375 1

WindowAgg (cost=522,602.53..523,127.86 rows=33,892 width=195) (actual time=35,239.037..35,610.059 rows=175,375 loops=1)

8. 113.123 35,248.461 ↓ 5.2 175,375 1

Sort (cost=522,602.53..522,619.48 rows=33,892 width=172) (actual time=35,238.955..35,248.461 rows=175,375 loops=1)

  • Sort Key: p.sku, ((date_range.date)::date)
  • Sort Method: quicksort Memory: 33588kB
9. 74.528 35,135.338 ↓ 5.2 175,375 1

Hash Left Join (cost=508,800.90..522,092.50 rows=33,892 width=172) (actual time=28,459.838..35,135.338 rows=175,375 loops=1)

  • Hash Cond: ((p.sku)::text = (base_row.sku)::text)
10. 1,276.328 29,202.704 ↓ 1,580.0 175,375 1

Nested Loop Left Join (cost=508,220.77..521,240.84 rows=111 width=88) (actual time=22,600.637..29,202.704 rows=175,375 loops=1)

  • Join Filter: (dpc.date = date_range.date)
  • Rows Removed by Join Filter: 7433622
11.          

CTE date_range

12. 0.087 0.087 ↑ 16.4 61 1

Function Scan on generate_series (cost=0.00..3.00 rows=1,000 width=8) (actual time=0.077..0.087 rows=61 loops=1)

13. 22.418 23,015.876 ↓ 1,580.0 175,375 1

Nested Loop (cost=508,217.66..520,783.79 rows=111 width=63) (actual time=22,599.375..23,015.876 rows=175,375 loops=1)

14. 100.498 22,927.333 ↓ 130.7 2,875 1

Merge Join (cost=508,217.66..520,775.14 rows=22 width=55) (actual time=22,599.280..22,927.333 rows=2,875 loops=1)

  • Merge Cond: ((p.sku)::text = (daily_product_costs.sku)::text)
15. 126.565 126.565 ↑ 1.2 5,061 1

Index Scan using products_seller_marketplace_isvalid_sku_id_idx on products p (cost=0.11..12,345.42 rows=6,106 width=55) (actual time=1.534..126.565 rows=5,061 loops=1)

  • Index Cond: (((seller_id)::text = 'A1LMQBEOGTEVAC'::text) AND ((marketplace_id)::text = 'ATVPDKIKX0DER'::text) AND (is_valid = true))
  • Filter: is_valid
16. 2,599.795 22,700.270 ↓ 2.7 565,561 1

Sort (cost=508,217.55..508,322.08 rows=209,059 width=14) (actual time=22,588.494..22,700.270 rows=565,561 loops=1)

  • Sort Key: daily_product_costs.sku
  • Sort Method: quicksort Memory: 68146kB
17. 3,037.680 20,100.475 ↓ 3.6 747,094 1

HashAggregate (cost=503,895.55..504,522.73 rows=209,059 width=14) (actual time=19,893.303..20,100.475 rows=747,094 loops=1)

  • Group Key: (daily_product_costs.sku)::text
18. 17,062.795 17,062.795 ↓ 1.0 5,424,066 1

Seq Scan on daily_product_costs (cost=0.00..501,237.48 rows=5,316,149 width=14) (actual time=0.065..17,062.795 rows=5,424,066 loops=1)

  • Filter: ((date >= '2019-06-09'::date) AND (date <= '2019-08-08'::date))
  • Rows Removed by Filter: 21458887
19. 66.125 66.125 ↓ 12.2 61 2,875

CTE Scan on date_range (cost=0.00..8.00 rows=5 width=8) (actual time=0.001..0.023 rows=61 loops=2,875)

  • Filter: (((date)::date >= '2019-06-09'::date) AND ((date)::date <= '2019-08-08'::date))
20. 4,910.500 4,910.500 ↓ 43.0 43 175,375

Index Scan using daily_product_costs_pkey on daily_product_costs dpc (cost=0.11..4.09 rows=1 width=47) (actual time=0.009..0.028 rows=43 loops=175,375)

  • Index Cond: (((seller_id)::text = 'A1LMQBEOGTEVAC'::text) AND ((marketplace_id)::text = 'ATVPDKIKX0DER'::text) AND ((sku)::text = (p.sku)::text))
21. 1.736 5,858.106 ↑ 12.4 4,927 1

Hash (cost=366.40..366.40 rows=61,066 width=116) (actual time=5,858.106..5,858.106 rows=4,927 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 819kB
22. 5,856.370 5,856.370 ↑ 12.4 4,927 1

CTE Scan on base_row_by_sku base_row (cost=0.00..366.40 rows=61,066 width=116) (actual time=5,798.270..5,856.370 rows=4,927 loops=1)

Planning time : 9.338 ms
Execution time : 35,733.359 ms