explain.depesz.com

PostgreSQL's explain analyze made readable

Result: nEq1

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 0.000 ↓ 5.0 65,220 1

Aggregate (cost=56,649.06..56,812.93 rows=13,110 width=48) (actual rows=65,220 loops=1)

2.          

CTE q_d

3. 0.000 0.000 ↑ 1.0 139,136 1

Bitmap Heap Scan on roomrates r (cost=1,540..13,958.93 rows=142,874 width=16) (actual rows=139,136 loops=1)

  • Heap Blocks: exact=927
4. 0.000 0.000 ↑ 1.0 139,136 1

Bitmap Index Scan on roomrates_hotel_index (cost=0..1,504.28 rows=142,874 width=0) (actual rows=139,136 loops=1)

  • Index Cond: (hotel = 14054)
5. 0.000 0.000 ↓ 5.0 65,220 1

Hash Join (cost=38,732.6..42,526.26 rows=13,110 width=48) (actual rows=65,220 loops=1)

6. 0.000 0.000 ↓ 194.9 139,136 1

CTE Scan on q_d d (cost=0..3,214.66 rows=714 width=16) (actual rows=139,136 loops=1)

  • Filter: (hotel = 14054)
7. 0.000 0.000 ↓ 5.0 65,220 1

Hash (cost=38,503.17..38,503.17 rows=13,110 width=31) (actual rows=65,220 loops=1)

8. 0.000 0.000 ↓ 5.0 65,220 1

Nested Loop (cost=1,199.43..38,503.17 rows=13,110 width=31) (actual rows=65,220 loops=1)

9. 0.000 0.000 ↓ 5.0 65,220 1

Hash Join (cost=1,199..3,794.45 rows=13,110 width=28) (actual rows=65,220 loops=1)

10. 0.000 0.000 ↓ 5.0 65,220 1

Hash Join (cost=1,163.36..3,689.96 rows=13,110 width=28) (actual rows=65,220 loops=1)

11. 0.000 0.000 ↓ 5.0 65,220 1

Nested Loop (cost=1.01..2,458.78 rows=13,110 width=28) (actual rows=65,220 loops=1)

12. 0.000 0.000 ↑ 1.0 30 1

Index Only Scan using days_pkey on days days (cost=0.29..9.41 rows=30 width=4) (actual rows=30 loops=1)

  • Index Cond: ((day >= ('now'::cstring)::date) AND (day <= (('now'::cstring)::date + '2 years -1 days'::interval)) AND (day >= '2020-09-01'::date) AND (day <= '2020-09-30'::date))
  • Heap Fetches: 30
13. 0.000 0.000 ↓ 5.0 2,174 30

Materialize (cost=0.71..2,286.59 rows=437 width=24) (actual rows=2,174 loops=30)

14. 0.000 0.000 ↓ 5.0 2,174 1

Nested Loop (cost=0.71..2,284.41 rows=437 width=24) (actual rows=2,174 loops=1)

15. 0.000 0.000 ↑ 44.0 1 1

Index Scan using rates_hotel_index on rates b (cost=0.29..72.5 rows=44 width=12) (actual rows=1 loops=1)

  • Index Cond: (hotel = 14054)
  • Filter: enabled
16. 0.000 0.000 ↓ 135.9 2,174 1

Index Scan using roomrates_rate on roomrates a (cost=0.43..50.11 rows=16 width=16) (actual rows=2,174 loops=1)

  • Index Cond: (rate = b.id)
  • Filter: (hotel = 14054)
17. 0.000 0.000 ↑ 1.0 38,214 1

Hash (cost=589.14..589.14 rows=38,214 width=12) (actual rows=38,214 loops=1)

18. 0.000 0.000 ↑ 1.0 38,214 1

Seq Scan on rulegroupsplanner h (cost=0..589.14 rows=38,214 width=12) (actual rows=38,214 loops=1)

19. 0.000 0.000 ↑ 1.0 42 1

Hash (cost=35.01..35.01 rows=42 width=12) (actual rows=42 loops=1)

20. 0.000 0.000 ↑ 1.0 42 1

Index Scan using rulegroups_hotel_index on rulegroups f (cost=0.29..35.01 rows=42 width=12) (actual rows=42 loops=1)

  • Index Cond: (hotel = 14054)
21. 0.000 0.000 ↑ 1.0 1 65,220

Index Scan using rulegrouprooms_rulegroup_roomguest_key on rulegrouprooms c (cost=0.43..2.65 rows=1 width=19) (actual rows=1 loops=65,220)

  • Index Cond: ((rulegroup = f.id) AND (roomguest = a.roomguest))