explain.depesz.com

PostgreSQL's explain analyze made readable

Result: w4bC

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 349,458.654 ↓ 2.8 552 1

Finalize GroupAggregate (cost=260,114,531.39..260,116,080.89 rows=200 width=2,396) (actual time=349,429.517..349,458.654 rows=552 loops=1)

  • Group Key: (((generate_series(((min(booking_daily_2018.first_observed)))::timestamp with time zone, ((max(booking_daily_2018.last_observed)))::timestamp with time zone, '1 day'::interval)))::date)
  • Functions: 219
  • Options: Inlining true, Optimization true, Expressions true, Deforming true
  • Timing: Generation 613.767 ms, Inlining 341.662 ms, Optimization 630585.797 ms, Emission 44640.010 ms, Total 676181.236 ms
2. 0.000 349,528.788 ↓ 3.7 2,208 1

Gather Merge (cost=260,114,531.39..260,114,602.39 rows=600 width=4,724) (actual time=349,428.003..349,528.788 rows=2,208 loops=1)

  • Workers Planned: 3
  • Workers Launched: 3
3. 14.368 1,086,201.308 ↓ 2.8 552 4

Sort (cost=260,113,531.35..260,113,531.85 rows=200 width=4,724) (actual time=271,550.145..271,550.327 rows=552 loops=4)

  • Sort Key: (((generate_series(((min(booking_daily_2018.first_observed)))::timestamp with time zone, ((max(booking_daily_2018.last_observed)))::timestamp with time zone, '1 day'::interval)))::date)
  • Sort Method: quicksort Memory: 4449kB
  • Worker 0: Sort Method: quicksort Memory: 4449kB
  • Worker 1: Sort Method: quicksort Memory: 4449kB
  • Worker 2: Sort Method: quicksort Memory: 4449kB
4. 259,648.840 1,086,186.940 ↓ 2.8 552 4

Partial HashAggregate (cost=260,113,521.71..260,113,523.71 rows=200 width=4,724) (actual time=271,543.626..271,546.735 rows=552 loops=4)

  • Group Key: (((generate_series(((min(booking_daily_2018.first_observed)))::timestamp with time zone, ((max(booking_daily_2018.last_observed)))::timestamp with time zone, '1 day'::interval)))::date)
5. 10,549.820 826,538.100 ↑ 3.1 11,192,076 4

Parallel Hash Join (cost=62,320.56..1,315,580.19 rows=35,079,355 width=22) (actual time=187,965.333..206,634.525 rows=11,192,076 loops=4)

  • Hash Cond: (q.house_pk = h.house_pk)
6. 16,061.556 66,388.156 ↑ 3.2 2,224,008 4

Merge Join (cost=57,097.82..1,060,007.25 rows=7,040,052 width=26) (actual time=564.579..16,597.039 rows=2,224,008 loops=4)

  • Merge Cond: (q.house_pk = s.house_pk)
7. 24.888 261.472 ↑ 1.7 4,132 4

Sort (cost=28,595.44..28,613.38 rows=7,175 width=14) (actual time=61.026..65.368 rows=4,132 loops=4)

  • Sort Key: q.house_pk
  • Sort Method: quicksort Memory: 805kB
  • Worker 0: Sort Method: quicksort Memory: 195kB
  • Worker 1: Sort Method: quicksort Memory: 240kB
  • Worker 2: Sort Method: quicksort Memory: 209kB
8. 2.628 236.584 ↑ 1.7 4,132 4

Parallel Append (cost=627.16..28,135.92 rows=7,175 width=14) (actual time=10.197..59.146 rows=4,132 loops=4)

9. 229.388 233.956 ↑ 1.7 4,132 4

Parallel Bitmap Heap Scan on booking_daily_2018 q (cost=627.16..28,100.05 rows=7,175 width=14) (actual time=10.185..58.489 rows=4,132 loops=4)

  • Recheck Cond: (date_in = '2018-12-10'::date)
  • Filter: (status_real > '-1'::integer)
  • Rows Removed by Filter: 2278
  • Heap Blocks: exact=13681
10. 4.568 4.568 ↑ 1.1 25,640 1

Bitmap Index Scan on booking_daily_2018_date_in_idx (cost=0.00..621.60 rows=28,535 width=0) (actual time=4.568..4.568 rows=25,640 loops=1)

  • Index Cond: (date_in = '2018-12-10'::date)
11. 22,339.952 50,065.128 ↑ 1.8 28,858,147 4

Materialize (cost=28,502.38..828,886.99 rows=52,836,000 width=12) (actual time=94.125..12,516.282 rows=28,858,147 loops=4)

12. 17,343.128 27,725.176 ↑ 1.8 28,858,147 4

Nested Loop (cost=28,502.38..696,796.99 rows=52,836,000 width=12) (actual time=94.109..6,931.294 rows=28,858,147 loops=4)

13. 344.240 344.240 ↑ 1.0 52,280 4

Index Scan using static_house_pk_idx on static s (cost=0.41..7,807.51 rows=52,836 width=8) (actual time=0.121..86.060 rows=52,280 loops=4)

  • Filter: (housetype_id = ANY ('{1,2,3,4,5}'::integer[]))
  • Rows Removed by Filter: 1056
14. 9,660.276 10,037.808 ↑ 1.8 552 209,121

Materialize (cost=28,501.96..28,541.99 rows=1,000 width=4) (actual time=0.002..0.048 rows=552 loops=209,121)

15. 0.792 377.532 ↑ 1.8 552 4

Result (cost=28,501.96..28,526.99 rows=1,000 width=4) (actual time=93.968..94.383 rows=552 loops=4)

16. 1.052 376.740 ↑ 1.8 552 4

ProjectSet (cost=28,501.96..28,506.99 rows=1,000 width=8) (actual time=93.960..94.185 rows=552 loops=4)

17. 0.180 375.688 ↑ 1.0 1 4

Finalize Aggregate (cost=28,501.96..28,501.97 rows=1 width=8) (actual time=93.922..93.922 rows=1 loops=4)

18. 0.008 375.508 ↑ 1.0 1 4

Append (cost=28,501.94..28,501.96 rows=1 width=8) (actual time=93.876..93.877 rows=1 loops=4)

19. 8.604 375.500 ↑ 1.0 1 4

Partial Aggregate (cost=28,501.94..28,501.95 rows=1 width=8) (actual time=93.875..93.875 rows=1 loops=4)

20. 366.896 366.896 ↑ 1.3 16,526 4

Index Scan using booking_daily_2018_date_in_idx on booking_daily_2018 (cost=0.56..28,390.73 rows=22,243 width=8) (actual time=0.108..91.724 rows=16,526 loops=4)

  • Index Cond: (date_in = '2018-12-10'::date)
  • Filter: (status_real > '-1'::integer)
  • Rows Removed by Filter: 9114
21. 73,953.420 749,600.124 ↑ 2.3 64,636 4

Parallel Hash (cost=3,329.55..3,329.55 rows=151,455 width=8) (actual time=187,400.030..187,400.031 rows=64,636 loops=4)

  • Buckets: 262144 Batches: 1 Memory Usage: 12256kB
22. 675,646.704 675,646.704 ↑ 2.3 64,636 4

Parallel Seq Scan on area_house h (cost=0.00..3,329.55 rows=151,455 width=8) (actual time=168,900.147..168,911.676 rows=64,636 loops=4)

Planning time : 138.766 ms
Execution time : 349,752.982 ms