explain.depesz.com

PostgreSQL's explain analyze made readable

Result: tdrb

Settings
# exclusive inclusive rows x rows loops node
1. 2,315.432 60,835.793 ↓ 153.7 1,400,056 1

HashAggregate (cost=73,916.17..74,030.02 rows=9,108 width=19) (actual time=60,281.936..60,835.793 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)
  • Buffers: shared hit=47,051,144 read=7,623, temp read=24,727 written=24,721
2. 16,218.016 58,520.361 ↓ 153.7 1,400,056 1

Nested Loop (cost=53,083.83..73,802.32 rows=9,108 width=19) (actual time=6,635.261..58,520.361 rows=1,400,056 loops=1)

  • Buffers: shared hit=47,051,144 read=7,623, temp read=24,727 written=24,721
3. 1,802.557 10,101.057 ↓ 153.7 1,400,056 1

Hash Join (cost=53,083.4..68,679.45 rows=9,108 width=31) (actual time=6,633.604..10,101.057 rows=1,400,056 loops=1)

  • Buffers: shared hit=2,942 read=7,623, temp read=24,727 written=24,721
4. 735.414 6,574.398 ↓ 153.7 1,400,056 1

Hash Join (cost=8,414.25..10,571.34 rows=9,108 width=28) (actual time=4,906.312..6,574.398 rows=1,400,056 loops=1)

  • Buffers: shared hit=2,373 read=177, temp read=14,374 written=14,374
5. 444.207 5,833.904 ↓ 153.7 1,400,056 1

Merge Join (cost=8,343.98..10,432.33 rows=9,108 width=28) (actual time=4,901.203..5,833.904 rows=1,400,056 loops=1)

  • Buffers: shared hit=2,369 read=177, temp read=14,374 written=14,374
6. 813.796 813.796 ↑ 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.013..813.796 rows=37,311 loops=1)

  • Buffers: shared hit=1,813
7. 3,263.603 4,575.901 ↓ 153.7 1,400,056 1

Sort (cost=8,343.69..8,366.46 rows=9,108 width=28) (actual time=4,086.804..4,575.901 rows=1,400,056 loops=1)

  • Sort Key: a.rate, days.day
  • Sort Method: external sort Disk: 57,496kB
  • Buffers: shared hit=556 read=177, temp read=14,374 written=14,374
8. 361.570 1,312.298 ↓ 153.7 1,400,056 1

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

  • Buffers: shared hit=556 read=177
9. 0.299 0.299 ↓ 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.032..0.299 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
  • Buffers: shared hit=3
10. 159.342 950.429 ↓ 147.0 60,872 23

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

  • Buffers: shared hit=553 read=177
11. 18.321 791.087 ↓ 147.0 60,872 1

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

  • Buffers: shared hit=553 read=177
12. 0.548 3.662 ↑ 1.6 28 1

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

  • Filter: b.enabled
  • Heap Blocks: exact=36
  • Buffers: shared hit=38
13. 3.114 3.114 ↑ 1.0 64 1

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

  • Index Cond: (b.hotel = 14,054)
  • Buffers: shared hit=2
14. 769.104 769.104 ↓ 144.9 2,174 28

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

  • Index Cond: (a.rate = b.id)
  • Filter: (a.hotel = 14,054)
  • Buffers: shared hit=515 read=177
15. 0.029 5.080 ↑ 1.0 42 1

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

  • Buffers: shared hit=4
16. 1.740 5.051 ↑ 1.0 42 1

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

  • Heap Blocks: exact=2
  • Buffers: shared hit=4
17. 3.311 3.311 ↑ 1.0 42 1

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

  • Index Cond: (f.hotel = 14,054)
  • Buffers: shared hit=2
18. 544.307 1,724.102 ↑ 1.0 1,187,766 1

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

  • Buffers: shared hit=569 read=7,446, temp written=4,434
19. 1,179.795 1,179.795 ↑ 1.0 1,187,766 1

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

  • Buffers: shared hit=569 read=7,446
20. 32,201.288 32,201.288 ↓ 16.0 32 1,400,056

Index Scan using roomrates_hotel_roomguest_rate on roomrates d (cost=0.43..0.52 rows=2 width=16) (actual time=0.004..0.023 rows=32 loops=1,400,056)

  • Index Cond: ((d.hotel = 14,054) AND (d.roomguest = c.roomguestref))
  • Buffers: shared hit=47,048,202
Planning time : 3.447 ms
Execution time : 60,921.755 ms