explain.depesz.com

PostgreSQL's explain analyze made readable

Result: e0i98

Settings
# exclusive inclusive rows x rows loops node
1. 848.676 2,535.899 ↑ 3.6 40,766 1

GroupAggregate (cost=634,911.03..659,698.69 rows=147,986 width=538) (actual time=1,214.245..2,535.899 rows=40,766 loops=1)

  • Group Key: res_staydt_aggregation.rsa_prop_code, res_staydt_aggregation.rsa_bkng_date, res_staydt_aggregation.rsa_stay_date, (COALESCE(room_pool_coll_def.
2. 1,110.697 1,687.223 ↓ 2.2 323,184 1

Sort (cost=634,911.03..635,281.00 rows=147,986 width=190) (actual time=1,214.185..1,687.223 rows=323,184 loops=1)

  • Sort Key: res_staydt_aggregation.rsa_bkng_date, res_staydt_aggregation.rsa_stay_date, (COALESCE(room_pool_coll_def.rpcd_room_pool_collection_id, '1'
  • Sort Method: external merge Disk: 51592kB
3. 82.112 576.526 ↓ 2.2 323,184 1

Hash Left Join (cost=27,101.06..608,545.66 rows=147,986 width=190) (actual time=129.578..576.526 rows=323,184 loops=1)

  • Hash Cond: (((res_staydt_aggregation.rsa_prop_code)::text = (rpgm_seg_map.rsm_prop_code)::text) AND ((res_staydt_aggregation.rsa_rpgm)::bpchar
4. 123.228 443.513 ↓ 2.2 323,184 1

Hash Left Join (cost=4,225.79..581,969.26 rows=147,986 width=136) (actual time=78.665..443.513 rows=323,184 loops=1)

  • Hash Cond: (((res_staydt_aggregation.rsa_prop_code)::text = (room_pool_coll_def.rpcd_prop_code)::text) AND ((res_staydt_aggregation.rsa_
5. 320.239 320.251 ↓ 2.2 323,184 1

Hash Left Join (cost=4,221.01..575,408.20 rows=147,986 width=131) (actual time=78.620..320.251 rows=323,184 loops=1)

  • Hash Cond: (((res_staydt_aggregation.rsa_prop_code)::text = (comp.crpd_prop_code)::text) AND ((res_staydt_aggregation.rsa_room_poo
  • -> Bitmap Heap Scan on res_staydt_aggregation (cost=4219.40..574293.19 rows=147986 width=118) (actual time=78.597..177.747 rows=
  • Recheck Cond: (((rsa_prop_code)::text = 'NYCMQ'::text) AND (rsa_stay_date >= '2019-07-13'::date) AND (rsa_stay_date <= '2020
  • Heap Blocks: exact=43371
  • -> Bitmap Index Scan on res_staydt_aggregation_rsa_prop_code_rsa_stay_date_idx (cost=0.00..4182.40 rows=147986 width=0) (a
  • Index Cond: (((rsa_prop_code)::text = 'NYCMQ'::text) AND (rsa_stay_date >= '2019-07-13'::date) AND (rsa_stay_date <= '
6. 0.001 0.012 ↓ 0.0 0 1

Hash (cost=1.59..1.59 rows=1 width=19) (actual time=0.012..0.012 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
7. 0.002 0.011 ↓ 0.0 0 1

Subquery Scan on comp (cost=1.56..1.59 rows=1 width=19) (actual time=0.011..0.011 rows=0 loops=1)

8. 0.000 0.009 ↓ 0.0 0 1

WindowAgg (cost=1.56..1.58 rows=1 width=39) (actual time=0.009..0.009 rows=0 loops=1)

9. 0.008 0.009 ↓ 0.0 0 1

Sort (cost=1.56..1.56 rows=1 width=11) (actual time=0.009..0.009 rows=0 loops=1)

  • Sort Key: cmpt_room_pool_def.crpd_cmpt_room_pool
  • Sort Method: quicksort Memory: 25kB
10. 0.001 0.001 ↓ 0.0 0 1

Result (cost=0.00..1.55 rows=1 width=11) (actual time=0.001..0.001 rows=0 loops=1)

  • One-Time Filter: false
11. 0.000 0.000 ↓ 0.0 0

Seq Scan on cmpt_room_pool_def (cost=0.00..1.55 rows=1 width=11) (never executed)

  • Filter: ((crpd_eff_end_ts IS NULL) AND ((crpd_prop_code)::text = 'WASGN'::text))
12. 0.034 0.034 ↓ 1.1 15 1

Hash (cost=4.57..4.57 rows=14 width=16) (actual time=0.034..0.034 rows=15 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • -> Index Only Scan using cnstr_pk_rpcd on room_pool_coll_def (cost=0.28..4.57 rows=14 width=16) (actual time=0.024..0.026 rows=1
  • Index Cond: ((rpcd_prop_code = 'NYCMQ'::text) AND (rpcd_data_flag = 'F'::bpchar))
  • Heap Fetches: 0
13. 0.000 50.901 ↓ 0.0 0 1

Hash (cost=22,875.15..22,875.15 rows=8 width=16) (actual time=50.901..50.901 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
14. 50.901 50.901 ↓ 0.0 0 1

Index Scan using cnstr_rsm_pk on rpgm_seg_map (cost=0.55..22,875.15 rows=8 width=16) (actual time=50.901..50.901 rows=0 loops=1)

  • Index Cond: (((rsm_prop_code)::text = 'NYCMQ'::text) AND (rsm_run_id = '1927019724842600'::bigint))
Planning time : 1.172 ms
Execution time : 2,545.344 ms