explain.depesz.com

PostgreSQL's explain analyze made readable

Result: mU6I

Settings
# exclusive inclusive rows x rows loops node
1. 0.044 5.245 ↓ 60.0 60 1

GroupAggregate (cost=517.10..517.13 rows=1 width=42) (actual time=5.192..5.245 rows=60 loops=1)

  • Group Key: pmd.pmd_prop_code, pmd.pmd_stay_date, room_pool.rmp_room_category, (date_part('isodow'::text, (pmd.pmd_stay_date)::timestamp without time zone))
2. 0.055 5.201 ↓ 60.0 60 1

Sort (cost=517.10..517.10 rows=1 width=42) (actual time=5.189..5.201 rows=60 loops=1)

  • Sort Key: pmd.pmd_stay_date, room_pool.rmp_room_category, (date_part('isodow'::text, (pmd.pmd_stay_date)::timestamp without time zone))
  • Sort Method: quicksort Memory: 29kB
3. 0.082 5.146 ↓ 60.0 60 1

Nested Loop (cost=508.99..517.09 rows=1 width=42) (actual time=4.553..5.146 rows=60 loops=1)

4. 0.264 4.884 ↓ 60.0 60 1

GroupAggregate (cost=508.72..508.77 rows=1 width=39) (actual time=4.537..4.884 rows=60 loops=1)

  • Group Key: pmd.pmd_prop_code, pmd.pmd_stay_date, pmd.pmd_room_pool
5. 0.419 4.620 ↓ 506.0 506 1

Sort (cost=508.72..508.72 rows=1 width=47) (actual time=4.519..4.620 rows=506 loops=1)

  • Sort Key: pmd.pmd_stay_date, pmd.pmd_room_pool
  • Sort Method: quicksort Memory: 96kB
6. 0.300 4.201 ↓ 506.0 506 1

Nested Loop Anti Join (cost=36.18..508.71 rows=1 width=47) (actual time=0.209..4.201 rows=506 loops=1)

7. 0.436 2.383 ↓ 506.0 506 1

Nested Loop (cost=35.91..506.94 rows=1 width=61) (actual time=0.193..2.383 rows=506 loops=1)

8. 0.190 0.429 ↓ 253.0 253 1

Hash Join (cost=35.48..494.45 rows=1 width=61) (actual time=0.174..0.429 rows=253 loops=1)

  • Hash Cond: (((pmd.pmd_hotel_code)::text = (pm.pmh_hotel_code)::text) AND (pmd.pmd_seq_num = pm.pmh_seq_num))
9. 0.089 0.197 ↓ 2.1 253 1

Bitmap Heap Scan on pj_mh_dtl pmd (cost=28.90..486.07 rows=118 width=55) (actual time=0.118..0.197 rows=253 loops=1)

  • Recheck Cond: (((pmd_prop_code)::text = 'SEATR'::text) AND (pmd_stay_date >= '2018-11-01'::date) AND (pmd_stay_date <= '2018-11-30'::date))
  • Heap Blocks: exact=8
10. 0.108 0.108 ↓ 2.1 253 1

Bitmap Index Scan on uix_pmd_1 (cost=0.00..28.87 rows=118 width=0) (actual time=0.108..0.108 rows=253 loops=1)

  • Index Cond: (((pmd_prop_code)::text = 'SEATR'::text) AND (pmd_stay_date >= '2018-11-01'::date) AND (pmd_stay_date <= '2018-11-30'::date))
11. 0.013 0.042 ↑ 4.1 15 1

Hash (cost=5.67..5.67 rows=61 width=14) (actual time=0.042..0.042 rows=15 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
12. 0.029 0.029 ↑ 4.1 15 1

Index Only Scan using uix_pmh_1 on pj_mh pm (cost=0.42..5.67 rows=61 width=14) (actual time=0.023..0.029 rows=15 loops=1)

  • Index Cond: ((pmh_prop_code = 'SEATR'::text) AND (pmh_grp_ctrct_ind = 'G'::text))
  • Heap Fetches: 0
13. 1.518 1.518 ↓ 2.0 2 253

Index Scan using ix_ppa on prop_param_assign ppa (cost=0.42..12.47 rows=1 width=6) (actual time=0.006..0.006 rows=2 loops=253)

  • Index Cond: (((ppa_prop_code)::text = 'SEATR'::text) AND ((ppa_param_id)::text = 'DATASRCIND'::text) AND ((ppa_active_ind)::text = 'Y'::text))
  • Filter: ((ppa_param_value)::text = 'M'::text)
14. 1.518 1.518 ↓ 0.0 0 506

Index Only Scan using uix_pmi_1 on pj_mh_include pmi (cost=0.28..1.02 rows=1 width=14) (actual time=0.003..0.003 rows=0 loops=506)

  • Index Cond: ((pmi_prop_code = (pm.pmh_prop_code)::text) AND (pmi_prop_code = 'SEATR'::text) AND (pmi_hotel_code = (pm.pmh_hotel_code)::text) AND (pmi_seq_num = pm.pmh_seq_num))
  • Heap Fetches: 0
15. 0.180 0.180 ↑ 1.0 1 60

Index Scan using uix_rmp_1 on room_pool (cost=0.28..8.30 rows=1 width=19) (actual time=0.003..0.003 rows=1 loops=60)

  • Index Cond: (((rmp_prop_code)::text = 'SEATR'::text) AND (rmp_room_pool = pmd.pmd_room_pool))