explain.depesz.com

PostgreSQL's explain analyze made readable

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

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 380.049 17,250.348 ↓ 1,580.0 175,375 1

WindowAgg (cost=525,721.41..526,183.49 rows=111 width=195) (actual time=16,677.930..17,250.348 rows=175,375 loops=1)

2.          

CTE base_row_by_sku

3. 19.636 771.647 ↓ 389.5 3,895 1

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

4. 493.598 752.011 ↓ 23,165.4 231,654 1

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

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

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

  • Hash Cond: ((dm.sku)::text = (p_1.sku)::text)
6. 183.558 183.558 ↓ 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=2.248..183.558 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. 1.511 4.011 ↑ 1.2 5,198 1

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

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

  • Index Cond: ((seller_id = 'A1LMQBEOGTEVAC'::text) AND (marketplace_id = 'ATVPDKIKX0DER'::text))
  • Heap Fetches: 389
9. 120.964 16,870.299 ↓ 1,580.0 175,375 1

Nested Loop Left Join (cost=521,954.33..522,414.75 rows=111 width=115) (actual time=16,677.877..16,870.299 rows=175,375 loops=1)

10. 61.083 16,749.335 ↓ 1,580.0 175,375 1

Merge Left Join (cost=521,954.19..521,954.38 rows=111 width=92) (actual time=16,677.841..16,749.335 rows=175,375 loops=1)

  • Merge Cond: (((costs.sku)::text = (base_date.sku)::text) AND (costs.date = base_date.date))
  • Join Filter: (((costs.seller_id)::text = 'A1LMQBEOGTEVAC'::text) AND ((costs.marketplace_id)::text = 'ATVPDKIKX0DER'::text))
11. 119.067 15,913.499 ↓ 1,580.0 175,375 1

Sort (cost=521,954.10..521,954.15 rows=111 width=88) (actual time=15,903.348..15,913.499 rows=175,375 loops=1)

  • Sort Key: costs.sku, costs.date
  • Sort Method: quicksort Memory: 30807kB
12. 32.225 15,794.432 ↓ 1,580.0 175,375 1

Subquery Scan on costs (cost=508,932.94..521,953.35 rows=111 width=88) (actual time=9,728.950..15,794.432 rows=175,375 loops=1)

13. 1,172.335 15,762.207 ↓ 1,580.0 175,375 1

Nested Loop Left Join (cost=508,932.94..521,953.01 rows=111 width=88) (actual time=9,728.947..15,762.207 rows=175,375 loops=1)

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

CTE date_range

15. 0.092 0.092 ↑ 16.4 61 1

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

16. 21.509 10,030.122 ↓ 1,580.0 175,375 1

Nested Loop (cost=508,929.83..521,495.96 rows=111 width=63) (actual time=9,728.811..10,030.122 rows=175,375 loops=1)

17. 99.350 9,945.363 ↓ 130.7 2,875 1

Merge Join (cost=508,929.83..521,487.31 rows=22 width=55) (actual time=9,728.695..9,945.363 rows=2,875 loops=1)

  • Merge Cond: ((p.sku)::text = (daily_product_costs.sku)::text)
18. 16.000 16.000 ↑ 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=0.098..16.000 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
19. 2,472.060 9,830.013 ↓ 2.7 565,744 1

Sort (cost=508,929.72..509,034.24 rows=209,059 width=14) (actual time=9,720.035..9,830.013 rows=565,744 loops=1)

  • Sort Key: daily_product_costs.sku
  • Sort Method: quicksort Memory: 68160kB
20. 2,494.096 7,357.953 ↓ 3.6 747,321 1

HashAggregate (cost=504,607.72..505,234.90 rows=209,059 width=14) (actual time=7,148.053..7,357.953 rows=747,321 loops=1)

  • Group Key: (daily_product_costs.sku)::text
21. 4,863.857 4,863.857 ↓ 1.0 5,426,613 1

Seq Scan on daily_product_costs (cost=0.00..501,945.89 rows=5,323,663 width=14) (actual time=0.026..4,863.857 rows=5,426,613 loops=1)

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

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

  • Filter: (((date)::date >= '2019-06-09'::date) AND ((date)::date <= '2019-08-08'::date))
23. 4,559.750 4,559.750 ↓ 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.026 rows=43 loops=175,375)

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

Sort (cost=0.09..0.10 rows=10 width=36) (actual time=774.477..774.753 rows=3,895 loops=1)

  • Sort Key: base_date.sku, base_date.date
  • Sort Method: quicksort Memory: 306kB
25. 772.929 772.929 ↓ 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=739.820..772.929 rows=3,895 loops=1)

26. 0.000 0.000 ↓ 0.0 0 175,375

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=175,375)

  • Index Cond: (((costs.seller_id)::text = (seller_id)::text) AND ((seller_id)::text = 'A1LMQBEOGTEVAC'::text) AND ((costs.marketplace_id)::text = (marketplace_id)::text) AND ((marketplace_id)::text = 'ATVPDKIKX0DER'::text) AND (base_date.date = date) AND ((costs.sku)::text = (sku)::text))
Planning time : 7.035 ms
Execution time : 17,271.341 ms