explain.depesz.com

PostgreSQL's explain analyze made readable

Result: geL3

Settings
# exclusive inclusive rows x rows loops node
1. 720.370 51,705.517 ↓ 1,561.1 382,470 1

WindowAgg (cost=309,099.10..309,102.90 rows=245 width=195) (actual time=50,894.075..51,705.517 rows=382,470 loops=1)

2.          

CTE base_date_by_sku

3. 603.876 36,605.837 ↓ 7.1 78,549 1

Unique (cost=203,982.65..203,993.64 rows=10,993 width=19) (actual time=35,463.599..36,605.837 rows=78,549 loops=1)

4. 14,976.582 36,001.961 ↓ 430.5 4,732,508 1

Sort (cost=203,982.65..203,988.15 rows=10,993 width=19) (actual time=35,463.591..36,001.961 rows=4,732,508 loops=1)

  • Sort Key: dm.sku, dm.date DESC
  • Sort Method: quicksort Memory: 560140kB
5. 2,737.019 21,025.379 ↓ 430.5 4,732,508 1

Hash Join (cost=8,116.34..203,835.08 rows=10,993 width=19) (actual time=234.791..21,025.379 rows=4,732,508 loops=1)

  • Hash Cond: ((dm.sku)::text = (p_1.sku)::text)
6. 18,059.186 18,059.186 ↑ 1.1 4,997,549 1

Index Only Scan using daily_product_metrics_ongoing_totals_pkey on daily_product_metrics_ongoing_totals dm (cost=0.14..192,962.27 rows=5,250,688 width=47) (actual time=4.420..18,059.186 rows=4,997,549 loops=1)

  • Index Cond: ((seller_id = 'A3TUJE1XKIZPOF'::text) AND (marketplace_id = 'ATVPDKIKX0DER'::text) AND (date >= '2019-04-09 00:00:00'::timestamp without time zone) AND (date <= '2019-06-08 00:00:00'::timestamp without time zone))
  • Heap Fetches: 521676
7. 69.966 229.174 ↓ 1.2 139,354 1

Hash (cost=7,693.58..7,693.58 rows=120,746 width=44) (actual time=229.173..229.174 rows=139,354 loops=1)

  • Buckets: 262144 (originally 131072) Batches: 1 (originally 1) Memory Usage: 12456kB
8. 159.208 159.208 ↓ 1.2 139,354 1

Index Only Scan using products_seller_marketplace_sku_unique on products p_1 (cost=0.11..7,693.58 rows=120,746 width=44) (actual time=0.630..159.208 rows=139,354 loops=1)

  • Index Cond: ((seller_id = 'A3TUJE1XKIZPOF'::text) AND (marketplace_id = 'ATVPDKIKX0DER'::text))
  • Heap Fetches: 3481
9. 1,292.645 50,985.147 ↓ 1,561.1 382,470 1

Sort (cost=105,105.46..105,105.59 rows=245 width=115) (actual time=50,894.003..50,985.147 rows=382,470 loops=1)

  • Sort Key: p.sku, ((date_range.date)::date)
  • Sort Method: quicksort Memory: 69286kB
10. 304.016 49,692.502 ↓ 1,561.1 382,470 1

Nested Loop Left Join (cost=4,006.71..105,103.52 rows=245 width=115) (actual time=36,932.028..49,692.502 rows=382,470 loops=1)

11. 317.200 45,181.316 ↓ 1,561.1 382,470 1

Hash Left Join (cost=4,006.57..104,087.38 rows=245 width=92) (actual time=36,931.225..45,181.316 rows=382,470 loops=1)

  • Hash Cond: ((p.sku)::text = (base_date.sku)::text)
  • Join Filter: (((p.seller_id)::text = 'A3TUJE1XKIZPOF'::text) AND ((p.marketplace_id)::text = 'ATVPDKIKX0DER'::text))
12. 1,143.513 8,206.690 ↓ 1,561.1 382,470 1

Nested Loop Left Join (cost=3,902.13..103,860.91 rows=245 width=88) (actual time=273.628..8,206.690 rows=382,470 loops=1)

  • Join Filter: (dpc.date = date_range.date)
  • Rows Removed by Join Filter: 3878756
13.          

CTE date_range

14. 0.107 0.107 ↑ 16.4 61 1

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

15. 93.886 561.187 ↓ 1,561.1 382,470 1

Nested Loop (cost=3,899.02..102,977.35 rows=245 width=63) (actual time=273.430..561.187 rows=382,470 loops=1)

16. 0.285 0.285 ↓ 12.2 61 1

CTE Scan on date_range (cost=0.00..8.00 rows=5 width=8) (actual time=0.045..0.285 rows=61 loops=1)

  • Filter: (((date)::date >= '2019-06-09'::date) AND ((date)::date <= '2019-08-08'::date))
17. 48.856 467.016 ↓ 128.0 6,270 61

Materialize (cost=3,899.02..102,968.52 rows=49 width=55) (actual time=4.482..7.656 rows=6,270 loops=61)

18. 1.717 418.160 ↓ 128.0 6,270 1

Nested Loop (cost=3,899.02..102,968.47 rows=49 width=55) (actual time=273.371..418.160 rows=6,270 loops=1)

19. 32.518 277.799 ↑ 3.9 6,302 1

HashAggregate (cost=3,898.90..3,971.73 rows=24,274 width=42) (actual time=273.216..277.799 rows=6,302 loops=1)

  • Group Key: (daily_product_costs.seller_id)::text, (daily_product_costs.marketplace_id)::text, (daily_product_costs.sku)::text
20. 245.281 245.281 ↓ 1.1 27,503 1

Index Only Scan using daily_product_costs_date_sku_currency_idx on daily_product_costs (cost=0.11..3,862.34 rows=24,373 width=42) (actual time=1.940..245.281 rows=27,503 loops=1)

  • Index Cond: ((seller_id = 'A3TUJE1XKIZPOF'::text) AND (marketplace_id = 'ATVPDKIKX0DER'::text) AND (date >= '2019-06-09'::date) AND (date <= '2019-08-08'::date))
  • Heap Fetches: 9549
21. 138.644 138.644 ↑ 1.0 1 6,302

Index Scan using products_seller_marketplace_sku_unique on products p (cost=0.11..4.08 rows=1 width=55) (actual time=0.022..0.022 rows=1 loops=6,302)

  • Index Cond: (((seller_id)::text = 'A3TUJE1XKIZPOF'::text) AND ((marketplace_id)::text = 'ATVPDKIKX0DER'::text) AND ((sku)::text = (daily_product_costs.sku)::text))
  • Filter: is_valid
  • Rows Removed by Filter: 0
22. 6,501.990 6,501.990 ↓ 10.0 10 382,470

Index Scan using daily_product_costs_pkey on daily_product_costs dpc (cost=0.11..3.59 rows=1 width=47) (actual time=0.010..0.017 rows=10 loops=382,470)

  • Index Cond: (((seller_id)::text = 'A3TUJE1XKIZPOF'::text) AND ((marketplace_id)::text = 'ATVPDKIKX0DER'::text) AND ((sku)::text = (p.sku)::text))
23. 18.159 36,657.426 ↓ 7.1 78,549 1

Hash (cost=65.96..65.96 rows=10,993 width=36) (actual time=36,657.426..36,657.426 rows=78,549 loops=1)

  • Buckets: 131072 (originally 16384) Batches: 1 (originally 1) Memory Usage: 5229kB
24. 36,639.267 36,639.267 ↓ 7.1 78,549 1

CTE Scan on base_date_by_sku base_date (cost=0.00..65.96 rows=10,993 width=36) (actual time=35,463.609..36,639.267 rows=78,549 loops=1)

25. 4,207.170 4,207.170 ↑ 1.0 1 382,470

Index Scan using daily_product_metrics_ongoing_totals_pkey on daily_product_metrics_ongoing_totals base_row (cost=0.14..4.15 rows=1 width=74) (actual time=0.011..0.011 rows=1 loops=382,470)

  • Index Cond: (((p.seller_id)::text = (seller_id)::text) AND ((seller_id)::text = 'A3TUJE1XKIZPOF'::text) AND ((p.marketplace_id)::text = (marketplace_id)::text) AND ((marketplace_id)::text = 'ATVPDKIKX0DER'::text) AND (base_date.date = date) AND ((p.sku)::text = (sku)::text))
Planning time : 7.096 ms
Execution time : 51,761.568 ms