explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 2rsB : Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: plan #CAuC; plan #G8nU; plan #sg7F; plan #qUEf; plan #ZH0v

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 350.949 12,765.885 ↓ 12,356.9 172,996 1

WindowAgg (cost=20,242.37..20,242.58 rows=14 width=195) (actual time=12,400.133..12,765.885 rows=172,996 loops=1)

2.          

CTE base_date_by_sku

3. 20.402 797.942 ↓ 389.5 3,895 1

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

4. 499.001 777.540 ↓ 23,165.4 231,654 1

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

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

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

  • Hash Cond: ((dm.sku)::text = (p_1.sku)::text)
6. 189.708 189.708 ↓ 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.060..189.708 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: 94411
7. 1.961 5.771 ↑ 1.2 5,198 1

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

  • Buckets: 8192 Batches: 1 Memory Usage: 430kB
8. 3.810 3.810 ↑ 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.038..3.810 rows=5,198 loops=1)

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

Sort (cost=16,475.29..16,475.30 rows=14 width=115) (actual time=12,400.075..12,414.936 rows=172,996 loops=1)

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

Nested Loop Left Join (cost=4,355.99..16,475.24 rows=14 width=115) (actual time=837.486..11,940.604 rows=172,996 loops=1)

11. 119.502 10,009.668 ↓ 12,356.9 172,996 1

Hash Left Join (cost=4,355.85..16,417.17 rows=14 width=92) (actual time=837.450..10,009.668 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,713.455 9,090.365 ↓ 12,356.9 172,996 1

Nested Loop Left Join (cost=4,355.76..16,416.97 rows=14 width=88) (actual time=37.629..9,090.365 rows=172,996 loops=1)

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

CTE date_range

14. 0.104 0.104 ↑ 16.4 61 1

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

15. 40.610 111.078 ↓ 12,356.9 172,996 1

Nested Loop (cost=4,352.64..16,356.70 rows=14 width=63) (actual time=37.334..111.078 rows=172,996 loops=1)

16. 0.257 0.257 ↓ 12.2 61 1

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

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

Materialize (cost=4,352.64..16,348.65 rows=3 width=55) (actual time=0.612..1.151 rows=2,836 loops=61)

18. 2.931 47.685 ↓ 945.3 2,836 1

Hash Semi Join (cost=4,352.64..16,348.65 rows=3 width=55) (actual time=37.283..47.685 rows=2,836 loops=1)

  • Hash Cond: ((p.sku)::text = (daily_product_costs.sku)::text)
19. 8.074 10.503 ↑ 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.786..10.503 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.429 2.429 ↑ 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.429..2.429 rows=5,100 loops=1)

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

Hash (cost=4,121.28..4,121.28 rows=26,195 width=42) (actual time=34.251..34.251 rows=23,289 loops=1)

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

Index Only Scan using daily_product_costs_date_sku_currency_idx on daily_product_costs (cost=0.11..4,121.28 rows=26,195 width=42) (actual time=0.061..24.748 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: 7578
23. 7,265.832 7,265.832 ↓ 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.042 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.739 799.801 ↓ 389.5 3,895 1

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

  • Buckets: 4096 (originally 1024) Batches: 1 (originally 1) Memory Usage: 218kB
25. 799.062 799.062 ↓ 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=765.184..799.062 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 : 6.860 ms
Execution time : 12,776.481 ms