explain.depesz.com

PostgreSQL's explain analyze made readable

Result: qANE

Settings
# exclusive inclusive rows x rows loops node
1. 705.142 288,364.612 ↓ 2.7 391,680 1

WindowAgg (cost=14,539,763.00..14,541,984.29 rows=143,309 width=195) (actual time=287,610.119..288,364.612 rows=391,680 loops=1)

2.          

CTE base_row_by_sku

3. 1,678.668 269,001.423 ↑ 1.4 82,880 1

Unique (cost=13,817,177.08..13,830,114.65 rows=116,443 width=103) (actual time=263,738.401..269,001.423 rows=82,880 loops=1)

4. 63,400.186 267,322.755 ↓ 1.3 16,178,143 1

Sort (cost=13,817,177.08..13,823,645.86 rows=12,937,574 width=103) (actual time=263,738.338..267,322.755 rows=16,178,143 loops=1)

  • Sort Key: daily_product_metrics_ongoing_totals.sku, daily_product_metrics_ongoing_totals.date DESC
  • Sort Method: external merge Disk: 1489360kB
5. 199,239.700 203,922.569 ↓ 1.3 16,178,143 1

Bitmap Heap Scan on daily_product_metrics_ongoing_totals (cost=326,620.95..13,511,526.06 rows=12,937,574 width=103) (actual time=5,428.065..203,922.569 rows=16,178,143 loops=1)

  • Recheck Cond: (((seller_id)::text = 'A3TUJE1XKIZPOF'::text) AND ((marketplace_id)::text = 'ATVPDKIKX0DER'::text) AND (date <= '2019-06-07 00:00:00'::timestamp without time zone))
  • Heap Blocks: exact=2013182
6. 4,682.869 4,682.869 ↓ 1.3 16,178,143 1

Bitmap Index Scan on dpm_ongoing_totals__sellerid__marketplaceid__date__parent_asin (cost=0.00..325,974.08 rows=12,937,574 width=0) (actual time=4,682.869..4,682.869 rows=16,178,143 loops=1)

  • Index Cond: (((seller_id)::text = 'A3TUJE1XKIZPOF'::text) AND ((marketplace_id)::text = 'ATVPDKIKX0DER'::text) AND (date <= '2019-06-07 00:00:00'::timestamp without time zone))
7. 1,211.241 287,659.470 ↓ 2.7 391,680 1

Sort (cost=709,648.35..709,720.00 rows=143,309 width=172) (actual time=287,609.990..287,659.470 rows=391,680 loops=1)

  • Sort Key: p.sku, ((date_range.date)::date)
  • Sort Method: quicksort Memory: 70493kB
8. 244.307 286,448.229 ↓ 2.7 391,680 1

Hash Left Join (cost=503,827.71..707,193.64 rows=143,309 width=172) (actual time=276,013.325..286,448.229 rows=391,680 loops=1)

  • Hash Cond: ((p.sku)::text = (base_row.sku)::text)
9. 1,010.884 17,151.084 ↓ 194.8 391,680 1

Nested Loop Left Join (cost=502,721.50..699,796.45 rows=2,011 width=88) (actual time=6,959.129..17,151.084 rows=391,680 loops=1)

  • Join Filter: (dpc.date = date_range.date)
  • Rows Removed by Join Filter: 3747387
10.          

CTE date_range

11. 0.091 0.091 ↑ 16.7 60 1

Function Scan on generate_series (cost=0.00..3.00 rows=1,000 width=8) (actual time=0.049..0.091 rows=60 loops=1)

12. 70.007 9,873.320 ↓ 194.8 391,680 1

Nested Loop (cost=502,718.39..692,479.38 rows=2,011 width=63) (actual time=6,957.902..9,873.320 rows=391,680 loops=1)

13. 0.273 0.273 ↓ 12.0 60 1

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

  • Filter: (((date)::date >= '2019-06-08'::date) AND ((date)::date <= '2019-08-06'::date))
14. 39.173 9,803.040 ↓ 16.2 6,528 60

Materialize (cost=502,718.39..692,464.54 rows=402 width=55) (actual time=115.965..163.384 rows=6,528 loops=60)

15. 181.001 9,763.867 ↓ 16.2 6,528 1

Hash Join (cost=502,718.39..692,464.14 rows=402 width=55) (actual time=6,957.836..9,763.867 rows=6,528 loops=1)

  • Hash Cond: ((p.sku)::text = (daily_product_costs.sku)::text)
16. 2,631.991 2,683.463 ↓ 1.3 131,467 1

Bitmap Heap Scan on products p (cost=2,581.55..192,271.36 rows=104,093 width=55) (actual time=56.909..2,683.463 rows=131,467 loops=1)

  • Recheck Cond: (((seller_id)::text = 'A3TUJE1XKIZPOF'::text) AND ((marketplace_id)::text = 'ATVPDKIKX0DER'::text))
  • Filter: is_valid
  • Heap Blocks: exact=25746
17. 51.472 51.472 ↓ 1.3 132,168 1

Bitmap Index Scan on products_seller_marketplace_isvalid_sku_id_idx (cost=0.00..2,576.35 rows=104,093 width=0) (actual time=51.472..51.472 rows=132,168 loops=1)

  • Index Cond: (((seller_id)::text = 'A3TUJE1XKIZPOF'::text) AND ((marketplace_id)::text = 'ATVPDKIKX0DER'::text) AND (is_valid = true))
18. 178.732 6,899.403 ↓ 3.6 725,332 1

Hash (cost=499,436.23..499,436.23 rows=200,173 width=14) (actual time=6,899.403..6,899.403 rows=725,332 loops=1)

  • Buckets: 1048576 (originally 262144) Batches: 1 (originally 1) Memory Usage: 42133kB
19. 2,029.709 6,720.671 ↓ 3.6 725,332 1

HashAggregate (cost=498,835.72..499,436.23 rows=200,173 width=14) (actual time=6,556.260..6,720.671 rows=725,332 loops=1)

  • Group Key: (daily_product_costs.sku)::text
20. 4,690.962 4,690.962 ↓ 1.0 5,167,107 1

Seq Scan on daily_product_costs (cost=0.00..496,278.00 rows=5,115,422 width=14) (actual time=0.049..4,690.962 rows=5,167,107 loops=1)

  • Filter: ((date >= '2019-06-08'::date) AND (date <= '2019-08-06'::date))
  • Rows Removed by Filter: 21372285
21. 6,266.880 6,266.880 ↓ 10.0 10 391,680

Index Scan using daily_product_costs_pkey on daily_product_costs dpc (cost=0.11..3.63 rows=1 width=47) (actual time=0.010..0.016 rows=10 loops=391,680)

  • Index Cond: (((seller_id)::text = 'A3TUJE1XKIZPOF'::text) AND ((marketplace_id)::text = 'ATVPDKIKX0DER'::text) AND ((sku)::text = (p.sku)::text))
22. 25.723 269,052.838 ↑ 1.4 82,880 1

Hash (cost=698.66..698.66 rows=116,443 width=116) (actual time=269,052.838..269,052.838 rows=82,880 loops=1)

  • Buckets: 131072 Batches: 1 Memory Usage: 5305kB
23. 269,027.115 269,027.115 ↑ 1.4 82,880 1

CTE Scan on base_row_by_sku base_row (cost=0.00..698.66 rows=116,443 width=116) (actual time=263,738.434..269,027.115 rows=82,880 loops=1)

Planning time : 7.838 ms
Execution time : 288,667.058 ms