explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 61QE

Settings
# exclusive inclusive rows x rows loops node
1. 4,115.518 4,115.518 ↓ 17,134.0 17,134 1

CTE Scan on monthly_values (cost=928,049.44..928,049.46 rows=1 width=188) (actual time=3,258.558..4,115.518 rows=17,134 loops=1)

2.          

CTE month_bounds

3. 487.100 925.519 ↑ 10.6 17,134 1

HashAggregate (cost=287,778.47..289,599.86 rows=182,139 width=37) (actual time=910.713..925.519 rows=17,134 loops=1)

  • Group Key: 20200101, agg_daily_pdaccount_product_changes.pd_account_id, agg_daily_pdaccount_product_changes.skunit
4. 437.336 438.419 ↓ 1.1 518,513 1

Bitmap Heap Scan on agg_daily_pdaccount_product_changes (cost=128.32..282,031.99 rows=459,718 width=33) (actual time=1.204..438.419 rows=518,513 loops=1)

  • Recheck Cond: ((date_id >= 20200101) AND (date_id <= 20200131))
  • Rows Removed by Index Recheck: 5859
  • Heap Blocks: lossy=12339
5. 1.083 1.083 ↑ 4.9 124,160 1

Bitmap Index Scan on agg_daily_pdaccount_product_changes_idx (cost=0.00..13.39 rows=614,178 width=0) (actual time=1.083..1.083 rows=124,160 loops=1)

  • Index Cond: ((date_id >= 20200101) AND (date_id <= 20200131))
6.          

CTE monthly_values

7. 436.517 4,086.446 ↓ 17,134.0 17,134 1

Hash Join (cost=351,374.08..638,449.59 rows=1 width=107) (actual time=3,258.546..4,086.446 rows=17,134 loops=1)

  • Hash Cond: ((ec.pd_account_id = sc.pd_account_id) AND (ec.skunit = sc.skunit) AND (ec.date_id = mb.max_date))
8. 393.872 395.066 ↓ 1.1 518,513 1

Bitmap Heap Scan on agg_daily_pdaccount_product_changes ec (cost=128.32..282,031.99 rows=459,718 width=67) (actual time=1.422..395.066 rows=518,513 loops=1)

  • Recheck Cond: ((date_id >= 20200101) AND (date_id <= 20200131))
  • Rows Removed by Index Recheck: 5859
  • Heap Blocks: lossy=12339
9. 1.194 1.194 ↑ 4.9 124,160 1

Bitmap Index Scan on agg_daily_pdaccount_product_changes_idx (cost=0.00..13.39 rows=614,178 width=0) (actual time=1.193..1.194 rows=124,160 loops=1)

  • Index Cond: ((date_id >= 20200101) AND (date_id <= 20200131))
10. 17.606 3,254.863 ↓ 503.9 17,134 1

Hash (cost=351,245.16..351,245.16 rows=34 width=98) (actual time=3,254.863..3,254.863 rows=17,134 loops=1)

  • Buckets: 32768 (originally 1024) Batches: 1 (originally 1) Memory Usage: 2459kB
11. 427.264 3,237.257 ↓ 503.9 17,134 1

Merge Join (cost=344,826.25..351,245.16 rows=34 width=98) (actual time=2,441.836..3,237.257 rows=17,134 loops=1)

  • Merge Cond: ((mb.pd_account_id = sc.pd_account_id) AND (mb.skunit = sc.skunit) AND (mb.min_date = sc.date_id))
12. 29.769 981.466 ↑ 10.6 17,134 1

Sort (cost=19,556.88..20,012.23 rows=182,139 width=52) (actual time=968.471..981.466 rows=17,134 loops=1)

  • Sort Key: mb.pd_account_id, mb.skunit, mb.min_date
  • Sort Method: quicksort Memory: 2246kB
13. 951.697 951.697 ↑ 10.6 17,134 1

CTE Scan on month_bounds mb (cost=0.00..3,642.78 rows=182,139 width=52) (actual time=910.716..951.697 rows=17,134 loops=1)

14. 1,368.559 1,828.527 ↓ 1.1 518,513 1

Sort (cost=325,269.37..326,418.66 rows=459,718 width=54) (actual time=1,473.354..1,828.527 rows=518,513 loops=1)

  • Sort Key: sc.pd_account_id, sc.skunit, sc.date_id
  • Sort Method: quicksort Memory: 85174kB
15. 459.377 459.968 ↓ 1.1 518,513 1

Bitmap Heap Scan on agg_daily_pdaccount_product_changes sc (cost=128.32..282,031.99 rows=459,718 width=54) (actual time=0.770..459.968 rows=518,513 loops=1)

  • Recheck Cond: ((date_id >= 20200101) AND (date_id <= 20200131))
  • Rows Removed by Index Recheck: 5859
  • Heap Blocks: lossy=12339
16. 0.591 0.591 ↑ 4.9 124,160 1

Bitmap Index Scan on agg_daily_pdaccount_product_changes_idx (cost=0.00..13.39 rows=614,178 width=0) (actual time=0.590..0.591 rows=124,160 loops=1)

  • Index Cond: ((date_id >= 20200101) AND (date_id <= 20200131))
Planning time : 0.899 ms
Execution time : 4,135.372 ms