explain.depesz.com

PostgreSQL's explain analyze made readable

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

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 69.473 1,423.832 ↓ 23,278.0 23,278 1

WindowAgg (cost=11,759.16..11,759.17 rows=1 width=195) (actual time=1,352.826..1,423.832 rows=23,278 loops=1)

2.          

CTE base_row_by_sku

3. 20.910 816.007 ↓ 389.5 3,895 1

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

4. 505.314 795.097 ↓ 23,165.4 231,654 1

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

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

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

  • Hash Cond: ((dm.sku)::text = (p_1.sku)::text)
6. 198.382 198.382 ↓ 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.056..198.382 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: 94409
7. 2.437 7.910 ↑ 1.2 5,198 1

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

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

  • Index Cond: ((seller_id = 'A1LMQBEOGTEVAC'::text) AND (marketplace_id = 'ATVPDKIKX0DER'::text))
  • Heap Fetches: 389
9. 61.467 1,354.359 ↓ 23,278.0 23,278 1

Sort (cost=7,992.08..7,992.08 rows=1 width=115) (actual time=1,352.774..1,354.359 rows=23,278 loops=1)

  • Sort Key: p.sku, ((date_range.date)::date)
  • Sort Method: quicksort Memory: 4042kB
10. 22.595 1,292.892 ↓ 23,278.0 23,278 1

Nested Loop Left Join (cost=4,334.21..7,992.08 rows=1 width=115) (actual time=981.378..1,292.892 rows=23,278 loops=1)

11. 9.673 1,270.297 ↓ 23,278.0 23,278 1

Hash Left Join (cost=4,334.07..7,987.93 rows=1 width=92) (actual time=981.329..1,270.297 rows=23,278 loops=1)

  • Hash Cond: (((p.sku)::text = (base_date.sku)::text) AND (((date_range.date)::date) = base_date.date))
  • Join Filter: (((p.seller_id)::text = 'A1LMQBEOGTEVAC'::text) AND ((p.marketplace_id)::text = 'ATVPDKIKX0DER'::text))
12. 12.680 442.297 ↓ 23,278.0 23,278 1

Nested Loop (cost=4,333.97..7,987.83 rows=1 width=88) (actual time=162.879..442.297 rows=23,278 loops=1)

  • Join Filter: ((daily_product_costs.sku)::text = (p.sku)::text)
13.          

CTE date_range

14. 0.065 0.065 ↑ 16.4 61 1

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

15. 12.031 220.016 ↓ 128.0 23,289 1

Hash Join (cost=4,330.86..7,271.47 rows=182 width=93) (actual time=162.817..220.016 rows=23,289 loops=1)

  • Hash Cond: ((dpc.sku)::text = (daily_product_costs.sku)::text)
16. 8.100 49.483 ↓ 16.1 23,289 1

Nested Loop (cost=14.85..2,953.83 rows=1,450 width=51) (actual time=4.031..49.483 rows=23,289 loops=1)

17. 0.147 0.147 ↓ 12.2 61 1

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

  • Filter: (((date)::date >= '2019-06-09'::date) AND ((date)::date <= '2019-08-08'::date))
18. 28.548 41.236 ↓ 1.3 382 61

Bitmap Heap Scan on daily_product_costs dpc (cost=14.85..588.30 rows=290 width=47) (actual time=0.243..0.676 rows=382 loops=61)

  • Recheck Cond: (((seller_id)::text = 'A1LMQBEOGTEVAC'::text) AND ((marketplace_id)::text = 'ATVPDKIKX0DER'::text) AND (date = date_range.date))
  • Heap Blocks: exact=16186
19. 12.688 12.688 ↓ 1.3 382 61

Bitmap Index Scan on daily_product_costs_date_sku_currency_idx (cost=0.00..14.84 rows=290 width=0) (actual time=0.208..0.208 rows=382 loops=61)

  • Index Cond: (((seller_id)::text = 'A1LMQBEOGTEVAC'::text) AND ((marketplace_id)::text = 'ATVPDKIKX0DER'::text) AND (date = date_range.date))
20. 1.357 158.502 ↑ 9.2 2,838 1

Hash (cost=4,224.75..4,224.75 rows=26,074 width=42) (actual time=158.501..158.502 rows=2,838 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 456kB
21. 20.201 157.145 ↑ 9.2 2,838 1

HashAggregate (cost=4,146.52..4,224.75 rows=26,074 width=42) (actual time=155.677..157.145 rows=2,838 loops=1)

  • Group Key: (daily_product_costs.seller_id)::text, (daily_product_costs.marketplace_id)::text, (daily_product_costs.sku)::text
22. 136.944 136.944 ↑ 1.1 23,289 1

Index Only Scan using daily_product_costs_date_sku_currency_idx on daily_product_costs (cost=0.11..4,107.24 rows=26,189 width=42) (actual time=0.089..136.944 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: 7513
23. 209.601 209.601 ↑ 1.0 1 23,289

Index Scan using products_seller_marketplace_sku_unique on products p (cost=0.11..3.92 rows=1 width=55) (actual time=0.009..0.009 rows=1 loops=23,289)

  • Index Cond: (((seller_id)::text = 'A1LMQBEOGTEVAC'::text) AND ((marketplace_id)::text = 'ATVPDKIKX0DER'::text) AND ((sku)::text = (dpc.sku)::text))
  • Filter: is_valid
  • Rows Removed by Filter: 0
24. 0.986 818.327 ↓ 389.5 3,895 1

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

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

CTE Scan on base_row_by_sku base_date (cost=0.00..0.06 rows=10 width=36) (actual time=781.127..817.341 rows=3,895 loops=1)

26. 0.000 0.000 ↓ 0.0 0 23,278

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.000..0.000 rows=0 loops=23,278)

  • 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.810 ms
Execution time : 1,425.931 ms