explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 9fwV

Settings
# exclusive inclusive rows x rows loops node
1. 44.141 5,706.599 ↓ 24,117.0 48,234 1

Subquery Scan on ratesforse0_ (cost=4,643.67..103,797.30 rows=2 width=305) (actual time=101.729..5,706.599 rows=48,234 loops=1)

2. 837.286 5,662.458 ↓ 24,117.0 48,234 1

Nested Loop (cost=4,643.67..103,797.28 rows=2 width=306) (actual time=101.724..5,662.458 rows=48,234 loops=1)

  • Join Filter: (rt.policy_id = cpr.cancellation_policy_id)
3. 305.960 4,391.066 ↓ 24,117.0 48,234 1

Nested Loop (cost=4,643.37..103,789.61 rows=2 width=302) (actual time=101.634..4,391.066 rows=48,234 loops=1)

4. 299.085 3,699.234 ↓ 24,117.0 48,234 1

Nested Loop (cost=4,643.06..103,782.96 rows=2 width=294) (actual time=101.600..3,699.234 rows=48,234 loops=1)

5. 313.429 2,917.809 ↓ 24,117.0 48,234 1

Nested Loop (cost=4,641.76..103,774.34 rows=2 width=302) (actual time=101.545..2,917.809 rows=48,234 loops=1)

6. 337.246 2,170.274 ↓ 24,117.0 48,234 1

Nested Loop Left Join (cost=4,641.45..103,767.70 rows=2 width=177) (actual time=101.501..2,170.274 rows=48,234 loops=1)

7. 352.495 1,350.688 ↓ 24,117.0 48,234 1

Nested Loop Left Join (cost=4,640.96..103,760.69 rows=2 width=169) (actual time=101.368..1,350.688 rows=48,234 loops=1)

8. 109.365 660.555 ↓ 24,117.0 48,234 1

Hash Join (cost=4,640.65..103,754.04 rows=2 width=85) (actual time=101.329..660.555 rows=48,234 loops=1)

  • Hash Cond: ((r.rate_type_id = rpt.rate_type) AND (r.room_id = rpt.room_id))
9. 453.650 485.351 ↓ 1.9 48,234 1

Bitmap Heap Scan on rates r (cost=1,548.47..99,298.57 rows=25,967 width=69) (actual time=35.220..485.351 rows=48,234 loops=1)

  • Recheck Cond: ((room_id = ANY ('{10852517,3325373,3319741,3330979,3330976,3308961,3308966,3330982,3330983,3308964,4105640,3325354,3308971,10856764,3308969
  • Filter: ((NOT close_out) AND ((price IS NOT NULL) OR (single_price IS NOT NULL)))
  • Rows Removed by Filter: 1,487
  • Heap Blocks: exact=15,816
10. 31.701 31.701 ↓ 1.5 49,723 1

Bitmap Index Scan on room_rate_day_index (cost=0.00..1,541.98 rows=33,062 width=0) (actual time=31.701..31.701 rows=49,723 loops=1)

  • Index Cond: ((room_id = ANY ('{10852517,3325373,3319741,3330979,3330976,3308961,3308966,3330982,3330983,3308964,4105640,3325354,3308971,10856764,330
11. 28.757 65.839 ↑ 1.0 81,299 1

Hash (cost=1,872.27..1,872.27 rows=81,327 width=16) (actual time=65.838..65.839 rows=81,299 loops=1)

  • Buckets: 131,072 Batches: 1 Memory Usage: 4,835kB
12. 37.082 37.082 ↑ 1.0 81,321 1

Seq Scan on room_price_types rpt (cost=0.00..1,872.27 rows=81,327 width=16) (actual time=0.020..37.082 rows=81,321 loops=1)

13. 144.702 337.638 ↑ 1.0 1 48,234

Bitmap Heap Scan on promotions p (cost=0.31..3.33 rows=1 width=92) (actual time=0.007..0.007 rows=1 loops=48,234)

  • Recheck Cond: (r.promotion = id)
  • Heap Blocks: exact=27,157
14. 192.936 192.936 ↑ 1.0 1 48,234

Bitmap Index Scan on promotions_pkey (cost=0.00..0.31 rows=1 width=0) (actual time=0.004..0.004 rows=1 loops=48,234)

  • Index Cond: (id = r.promotion)
15. 144.702 482.340 ↑ 1.0 1 48,234

Bitmap Heap Scan on hotel_financial_infos hfi (cost=0.49..3.51 rows=1 width=16) (actual time=0.010..0.010 rows=1 loops=48,234)

  • Recheck Cond: (r.hotel_id = hotel_id)
  • Heap Blocks: exact=48,234
16. 337.638 337.638 ↑ 1.0 1 48,234

Bitmap Index Scan on uk_e04mwqnw2eexcykskv8i12nr2 (cost=0.00..0.49 rows=1 width=0) (actual time=0.007..0.007 rows=1 loops=48,234)

  • Index Cond: (hotel_id = r.hotel_id)
17. 192.936 434.106 ↑ 1.0 1 48,234

Bitmap Heap Scan on rate_types rt (cost=0.30..3.32 rows=1 width=141) (actual time=0.009..0.009 rows=1 loops=48,234)

  • Recheck Cond: (id = r.rate_type_id)
  • Filter: ((market_place)::text = 'Online'::text)
  • Heap Blocks: exact=48,234
18. 241.170 241.170 ↑ 1.0 1 48,234

Bitmap Index Scan on rate_types_pkey (cost=0.00..0.30 rows=1 width=0) (actual time=0.005..0.005 rows=1 loops=48,234)

  • Index Cond: (id = r.rate_type_id)
19. 192.936 482.340 ↑ 1.0 1 48,234

Bitmap Heap Scan on rooms roo (cost=1.30..4.31 rows=1 width=8) (actual time=0.010..0.010 rows=1 loops=48,234)

  • Recheck Cond: (id = rpt.room_id)
  • Filter: ((status)::text = 'ACTIVE'::text)
  • Heap Blocks: exact=48,234
20. 289.404 289.404 ↑ 1.0 1 48,234

Bitmap Index Scan on rooms_pkey (cost=0.00..1.30 rows=1 width=0) (actual time=0.006..0.006 rows=1 loops=48,234)

  • Index Cond: (id = rpt.room_id)
21. 144.702 385.872 ↑ 1.0 1 48,234

Bitmap Heap Scan on cancellation_policies cp (cost=0.31..3.32 rows=1 width=8) (actual time=0.008..0.008 rows=1 loops=48,234)

  • Recheck Cond: (id = rt.policy_id)
  • Heap Blocks: exact=48,234
22. 241.170 241.170 ↑ 1.0 1 48,234

Bitmap Index Scan on cancellation_policies_pkey (cost=0.00..0.31 rows=1 width=0) (actual time=0.005..0.005 rows=1 loops=48,234)

  • Index Cond: (id = rt.policy_id)
23. 192.936 434.106 ↑ 1.0 1 48,234

Bitmap Heap Scan on cancellation_policy_rules cpr (cost=0.31..3.32 rows=1 width=19) (actual time=0.008..0.009 rows=1 loops=48,234)

  • Recheck Cond: (cancellation_policy_id = cp.id)
  • Heap Blocks: exact=48,234
24. 241.170 241.170 ↑ 1.0 1 48,234

Bitmap Index Scan on cancellation_policy_rules_cancellation_policy_id_idx (cost=0.00..0.31 rows=1 width=0) (actual time=0.005..0.005 rows=1 loops=48,234)

  • Index Cond: (cancellation_policy_id = cp.id)
Planning time : 13.608 ms
Execution time : 5,722.324 ms