explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 2ydu

Settings
# exclusive inclusive rows x rows loops node
1. 349.432 12,284.414 ↓ 12,356.9 172,996 1

WindowAgg (cost=20,252.42..20,252.64 rows=14 width=195) (actual time=11,920.411..12,284.414 rows=172,996 loops=1)

2.          

CTE base_date_by_sku

3. 21.020 809.217 ↓ 389.5 3,895 1

Unique (cost=3,767.07..3,767.08 rows=10 width=19) (actual time=774.574..809.217 rows=3,895 loops=1)

4. 507.400 788.197 ↓ 23,165.4 231,654 1

Sort (cost=3,767.07..3,767.07 rows=10 width=19) (actual time=774.570..788.197 rows=231,654 loops=1)

  • Sort Key: dm.sku, dm.date DESC
  • Sort Method: quicksort Memory: 18534kB
5. 82.360 280.797 ↓ 23,165.4 231,654 1

Hash Join (cost=434.00..3,767.03 rows=10 width=19) (actual time=5.816..280.797 rows=231,654 loops=1)

  • Hash Cond: ((dm.sku)::text = (p_1.sku)::text)
6. 193.020 193.020 ↓ 3.3 294,606 1

Index Only Scan using daily_product_metrics_ongoing_totals_pkey on daily_product_metrics_ongoing_totals dm (cost=0.14..3,286.26 rows=89,354 width=47) (actual time=0.102..193.020 rows=294,606 loops=1)

  • Index Cond: ((seller_id = 'A1LMQBEOGTEVAC'::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: 94412
7. 1.838 5.417 ↑ 1.2 5,198 1

Hash (cost=411.59..411.59 rows=6,362 width=44) (actual time=5.417..5.417 rows=5,198 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 430kB
8. 3.579 3.579 ↑ 1.2 5,198 1

Index Only Scan using products_seller_marketplace_sku_unique on products p_1 (cost=0.11..411.59 rows=6,362 width=44) (actual time=0.033..3.579 rows=5,198 loops=1)

  • Index Cond: ((seller_id = 'A1LMQBEOGTEVAC'::text) AND (marketplace_id = 'ATVPDKIKX0DER'::text))
  • Heap Fetches: 389
9. 471.288 11,934.982 ↓ 12,356.9 172,996 1

Sort (cost=16,485.35..16,485.35 rows=14 width=115) (actual time=11,920.351..11,934.982 rows=172,996 loops=1)

  • Sort Key: p.sku, ((date_range.date)::date)
  • Sort Method: quicksort Memory: 33253kB
10. 161.567 11,463.694 ↓ 12,356.9 172,996 1

Nested Loop Left Join (cost=4,366.05..16,485.29 rows=14 width=115) (actual time=881.395..11,463.694 rows=172,996 loops=1)

11. 115.095 9,572.167 ↓ 12,356.9 172,996 1

Hash Left Join (cost=4,365.91..16,427.23 rows=14 width=92) (actual time=881.313..9,572.167 rows=172,996 loops=1)

  • Hash Cond: ((p.sku)::text = (base_date.sku)::text)
  • Join Filter: (((p.seller_id)::text = 'A1LMQBEOGTEVAC'::text) AND ((p.marketplace_id)::text = 'ATVPDKIKX0DER'::text))
12. 1,587.765 8,645.827 ↓ 12,356.9 172,996 1

Nested Loop Left Join (cost=4,365.81..16,427.03 rows=14 width=88) (actual time=70.021..8,645.827 rows=172,996 loops=1)

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

CTE date_range

14. 0.131 0.131 ↑ 16.4 61 1

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

15. 36.992 138.222 ↓ 12,356.9 172,996 1

Nested Loop (cost=4,362.70..16,366.76 rows=14 width=63) (actual time=69.701..138.222 rows=172,996 loops=1)

16. 0.275 0.275 ↓ 12.2 61 1

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

  • Filter: (((date)::date >= '2019-06-09'::date) AND ((date)::date <= '2019-08-08'::date))
17. 22.275 100.955 ↓ 945.3 2,836 61

Materialize (cost=4,362.70..16,358.71 rows=3 width=55) (actual time=1.142..1.655 rows=2,836 loops=61)

18. 3.629 78.680 ↓ 945.3 2,836 1

Hash Semi Join (cost=4,362.70..16,358.71 rows=3 width=55) (actual time=69.642..78.680 rows=2,836 loops=1)

  • Hash Cond: ((p.sku)::text = (daily_product_costs.sku)::text)
19. 6.057 8.072 ↑ 1.2 5,061 1

Bitmap Heap Scan on products p (cost=139.68..12,132.38 rows=6,106 width=55) (actual time=2.394..8.072 rows=5,061 loops=1)

  • Recheck Cond: (((seller_id)::text = 'A1LMQBEOGTEVAC'::text) AND ((marketplace_id)::text = 'ATVPDKIKX0DER'::text))
  • Filter: is_valid
  • Heap Blocks: exact=1414
20. 2.015 2.015 ↑ 1.2 5,100 1

Bitmap Index Scan on products_seller_marketplace_isvalid_sku_id_idx (cost=0.00..139.38 rows=6,106 width=0) (actual time=2.015..2.015 rows=5,100 loops=1)

  • Index Cond: (((seller_id)::text = 'A1LMQBEOGTEVAC'::text) AND ((marketplace_id)::text = 'ATVPDKIKX0DER'::text) AND (is_valid = true))
21. 10.580 66.979 ↑ 1.1 23,289 1

Hash (cost=4,131.31..4,131.31 rows=26,201 width=42) (actual time=66.978..66.979 rows=23,289 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 1876kB
22. 56.399 56.399 ↑ 1.1 23,289 1

Index Only Scan using daily_product_costs_date_sku_currency_idx on daily_product_costs (cost=0.11..4,131.31 rows=26,201 width=42) (actual time=0.115..56.399 rows=23,289 loops=1)

  • Index Cond: ((seller_id = 'A1LMQBEOGTEVAC'::text) AND (marketplace_id = 'ATVPDKIKX0DER'::text) AND (date >= '2019-06-09'::date) AND (date <= '2019-08-08'::date))
  • Heap Fetches: 7628
23. 6,919.840 6,919.840 ↓ 43.0 43 172,996

Index Scan using daily_product_costs_pkey on daily_product_costs dpc (cost=0.11..4.09 rows=1 width=47) (actual time=0.011..0.040 rows=43 loops=172,996)

  • Index Cond: (((seller_id)::text = 'A1LMQBEOGTEVAC'::text) AND ((marketplace_id)::text = 'ATVPDKIKX0DER'::text) AND ((sku)::text = (p.sku)::text))
24. 0.795 811.245 ↓ 389.5 3,895 1

Hash (cost=0.06..0.06 rows=10 width=36) (actual time=811.244..811.245 rows=3,895 loops=1)

  • Buckets: 4096 (originally 1024) Batches: 1 (originally 1) Memory Usage: 218kB
25. 810.450 810.450 ↓ 389.5 3,895 1

CTE Scan on base_date_by_sku base_date (cost=0.00..0.06 rows=10 width=36) (actual time=774.583..810.450 rows=3,895 loops=1)

26. 1,729.960 1,729.960 ↑ 1.0 1 172,996

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.010..0.010 rows=1 loops=172,996)

  • Index Cond: (((p.seller_id)::text = (seller_id)::text) AND ((seller_id)::text = 'A1LMQBEOGTEVAC'::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.777 ms
Execution time : 12,294.919 ms