explain.depesz.com

PostgreSQL's explain analyze made readable

Result: OAJ5

Settings
# exclusive inclusive rows x rows loops node
1. 1,588.432 8,715.206 ↓ 153.7 1,400,056 1

HashAggregate (cost=87,217.51..87,331.36 rows=9,108 width=19) (actual time=8,168.212..8,715.206 rows=1,400,056 loops=1)

  • Group Key: days.day, a.id, COALESCE(d.id, a.id), COALESCE(c.formula, 'x'::character varying), NULLIF(COALESCE(d.id, a.id), a.id)
2.          

CTE d

3. 21.706 32.804 ↓ 1.0 139,136 1

Bitmap Heap Scan on roomrates roomrates (cost=2,543.21..14,871.02 rows=135,585 width=54) (actual time=11.256..32.804 rows=139,136 loops=1)

  • Heap Blocks: exact=927
4. 11.098 11.098 ↓ 1.0 139,136 1

Bitmap Index Scan on roomrates_hotel_index (cost=0..2,509.31 rows=135,585 width=0) (actual time=11.098..11.098 rows=139,136 loops=1)

  • Index Cond: (roomrates.hotel = 14,054)
5. 885.208 7,126.774 ↓ 153.7 1,400,056 1

Hash Join (cost=68,838.84..72,232.64 rows=9,108 width=19) (actual time=6,171.999..7,126.774 rows=1,400,056 loops=1)

6. 80.978 80.978 ↓ 205.2 139,136 1

CTE Scan on d d (cost=0..3,050.66 rows=678 width=16) (actual time=11.264..80.978 rows=139,136 loops=1)

  • Filter: (d.hotel = 14,054)
7. 541.636 6,160.588 ↓ 153.7 1,400,056 1

Hash (cost=68,679.45..68,679.45 rows=9,108 width=31) (actual time=6,160.588..6,160.588 rows=1,400,056 loops=1)

8. 1,094.612 5,618.952 ↓ 153.7 1,400,056 1

Hash Join (cost=53,083.4..68,679.45 rows=9,108 width=31) (actual time=3,248.547..5,618.952 rows=1,400,056 loops=1)

9. 603.462 3,812.200 ↓ 153.7 1,400,056 1

Hash Join (cost=8,414.25..10,571.34 rows=9,108 width=28) (actual time=2,535.635..3,812.2 rows=1,400,056 loops=1)

10. 346.227 3,208.671 ↓ 153.7 1,400,056 1

Merge Join (cost=8,343.98..10,432.33 rows=9,108 width=28) (actual time=2,535.531..3,208.671 rows=1,400,056 loops=1)

11. 9.205 9.205 ↑ 1.0 37,311 1

Index Scan using rulegroupsplanner_pkey on rulegroupsplanner h (cost=0.29..1,829.22 rows=38,214 width=12) (actual time=0.016..9.205 rows=37,311 loops=1)

12. 2,434.541 2,853.239 ↓ 153.7 1,400,056 1

Sort (cost=8,343.69..8,366.46 rows=9,108 width=28) (actual time=2,523.839..2,853.239 rows=1,400,056 loops=1)

  • Sort Key: a.rate, days.day
  • Sort Method: external sort Disk: 57,496kB
13. 259.556 418.698 ↓ 153.7 1,400,056 1

Nested Loop (cost=5.5..7,744.71 rows=9,108 width=28) (actual time=0.11..418.698 rows=1,400,056 loops=1)

14. 0.212 0.212 ↓ 1.0 23 1

Index Only Scan using days_pkey on days days (cost=0.29..26.55 rows=22 width=4) (actual time=0.049..0.212 rows=23 loops=1)

  • Index Cond: ((days.day >= ('now'::cstring)::date) AND (days.day <= (('now'::cstring)::date + '2 years -1 days'::interval)) AND (days.day >= '2020-01-01'::date) AND (days.day <= '2020-01-31'::date))
  • Heap Fetches: 23
15. 120.199 158.930 ↓ 147.0 60,872 23

Materialize (cost=5.2..7,605.35 rows=414 width=24) (actual time=0.003..6.91 rows=60,872 loops=23)

16. 10.650 38.731 ↓ 147.0 60,872 1

Nested Loop (cost=5.2..7,603.28 rows=414 width=24) (actual time=0.057..38.731 rows=60,872 loops=1)

17. 0.170 0.193 ↑ 1.6 28 1

Bitmap Heap Scan on rates b (cost=4.78..227.4 rows=44 width=12) (actual time=0.038..0.193 rows=28 loops=1)

  • Filter: b.enabled
  • Heap Blocks: exact=36
18. 0.023 0.023 ↑ 1.0 64 1

Bitmap Index Scan on rates_hotel_index (cost=0..4.77 rows=64 width=0) (actual time=0.023..0.023 rows=64 loops=1)

  • Index Cond: (b.hotel = 14,054)
19. 27.888 27.888 ↓ 144.9 2,174 28

Index Scan using roomrates_rate on roomrates a (cost=0.43..167.48 rows=15 width=16) (actual time=0.011..0.996 rows=2,174 loops=28)

  • Index Cond: (a.rate = b.id)
  • Filter: (a.hotel = 14,054)
20. 0.013 0.067 ↑ 1.0 42 1

Hash (cost=69.64..69.64 rows=42 width=12) (actual time=0.067..0.067 rows=42 loops=1)

21. 0.027 0.054 ↑ 1.0 42 1

Bitmap Heap Scan on rulegroups f (cost=4.61..69.64 rows=42 width=12) (actual time=0.035..0.054 rows=42 loops=1)

  • Heap Blocks: exact=2
22. 0.027 0.027 ↑ 1.0 42 1

Bitmap Index Scan on rulegroups_hotel_index (cost=0..4.6 rows=42 width=0) (actual time=0.027..0.027 rows=42 loops=1)

  • Index Cond: (f.hotel = 14,054)
23. 399.504 712.140 ↑ 1.0 1,187,766 1

Hash (cost=19,892.66..19,892.66 rows=1,187,766 width=19) (actual time=712.14..712.14 rows=1,187,766 loops=1)

24. 312.636 312.636 ↑ 1.0 1,187,766 1

Seq Scan on rulegrouprooms c (cost=0..19,892.66 rows=1,187,766 width=19) (actual time=0.02..312.636 rows=1,187,766 loops=1)

Planning time : 1.802 ms
Execution time : 8,778.589 ms