explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 2W2Q

Settings
# exclusive inclusive rows x rows loops node
1. 0.860 2,337.853 ↓ 14.0 14 1

GroupAggregate (cost=4,893.21..4,893.25 rows=1 width=150) (actual time=2,337.124..2,337.853 rows=14 loops=1)

  • Group Key: pf.cid, pf.floorplan_name, (COALESCE(sum(CASE WHEN ((sc.id IS NOT NULL) AND (ca.lease_status_type_id = 3) AND (ca.unit_space_id IS NULL)) THEN (sc.unit_space_count)::bigint WHEN (ca.lease_status_type_id > 3) THEN (count(*)) ELSE '1'::bigint END) FILTER (WHERE (ca.lease_interval_type_id = 1)), '0'::numeric)), (COALESCE(sum(CASE WHEN ((sc.id IS NOT NULL) AND (ca.lease_status_type_id = 3) AND (ca.unit_space_id IS NULL)) THEN (sc.unit_space_count)::bigint WHEN (ca.lease_status_type_id > 3) THEN (count(*)) ELSE '1'::bigint END) FILTER (WHERE (ca.lease_interval_type_id = 3)), '0'::numeric)), (array_agg(ca.id))
2. 2.222 2,336.993 ↓ 1,356.0 1,356 1

Sort (cost=4,893.21..4,893.22 rows=1 width=114) (actual time=2,336.910..2,336.993 rows=1,356 loops=1)

  • Sort Key: pf.floorplan_name, (COALESCE(sum(CASE WHEN ((sc.id IS NOT NULL) AND (ca.lease_status_type_id = 3) AND (ca.unit_space_id IS NULL)) THEN (sc.unit_space_count)::bigint WHEN (ca.lease_status_type_id > 3) THEN (count(*)) ELSE '1'::bigint END) FILTER (WHERE (ca.lease_interval_type_id = 1)), '0'::numeric)), (COALESCE(sum(CASE WHEN ((sc.id IS NOT NULL) AND (ca.lease_status_type_id = 3) AND (ca.unit_space_id IS NULL)) THEN (sc.unit_space_count)::bigint WHEN (ca.lease_status_type_id > 3) THEN (count(*)) ELSE '1'::bigint END) FILTER (WHERE (ca.lease_interval_type_id = 3)), '0'::numeric)), (array_agg(ca.id))
  • Sort Method: quicksort Memory: 194kB
3. 0.613 2,334.771 ↓ 1,356.0 1,356 1

Nested Loop (cost=4,088.29..4,893.20 rows=1 width=114) (actual time=315.095..2,334.771 rows=1,356 loops=1)

4. 0.126 2,326.140 ↓ 19.0 19 1

Nested Loop Left Join (cost=4,087.86..4,888.64 rows=1 width=118) (actual time=315.006..2,326.140 rows=19 loops=1)

5. 0.101 13.315 ↓ 19.0 19 1

Nested Loop (cost=3.64..804.39 rows=1 width=22) (actual time=0.885..13.315 rows=19 loops=1)

6. 1.232 13.043 ↓ 19.0 19 1

Hash Join (cost=3.64..803.35 rows=1 width=26) (actual time=0.843..13.043 rows=19 loops=1)

  • Hash Cond: (ut.property_floorplan_id = pf.id)
7. 11.518 11.518 ↑ 1.0 3,441 1

Index Scan using pk_unit_types on unit_types ut (cost=0.29..790.92 rows=3,459 width=8) (actual time=0.120..11.518 rows=3,441 loops=1)

  • Index Cond: (cid = 12924)
8. 0.023 0.293 ↓ 11.5 23 1

Hash (cost=3.33..3.33 rows=2 width=22) (actual time=0.293..0.293 rows=23 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
9. 0.270 0.270 ↓ 11.5 23 1

Index Scan using idx_property_floorplans_mid_pid on property_floorplans pf (cost=0.29..3.33 rows=2 width=22) (actual time=0.081..0.270 rows=23 loops=1)

  • Index Cond: ((cid = 12924) AND (property_id = 221633))
  • Filter: (deleted_by IS NULL)
  • Rows Removed by Filter: 2
10. 0.171 0.171 ↑ 1.0 1 19

Seq Scan on leasing_goals lg (cost=0.00..1.03 rows=1 width=8) (actual time=0.007..0.009 rows=1 loops=19)

  • Filter: ((cid = 12924) AND (property_id = 221633) AND (id = 35))
  • Rows Removed by Filter: 3
11. 0.342 2,312.699 ↑ 1.0 1 19

Aggregate (cost=4,084.22..4,084.23 rows=1 width=96) (actual time=121.720..121.721 rows=1 loops=19)

12. 0.108 2,312.357 ↓ 0.0 0 19

Nested Loop Left Join (cost=2,501.84..4,084.19 rows=1 width=32) (actual time=104.481..121.703 rows=0 loops=19)

13. 18.076 2,311.673 ↓ 0.0 0 19

Nested Loop Left Join (cost=2,501.41..4,081.70 rows=1 width=36) (actual time=104.450..121.667 rows=0 loops=19)

14. 1,149.861 2,293.357 ↓ 0.0 0 19

Bitmap Heap Scan on cached_applications ca (cost=2,501.13..4,078.82 rows=1 width=32) (actual time=103.488..120.703 rows=0 loops=19)

  • Recheck Cond: ((cid = pf.cid) AND (lease_status_type_id = ANY ('{3,4,5}'::integer[])))
  • Filter: ((lease_approved_on IS NOT NULL) AND (lease_interval_type_id = ANY ('{1,3}'::integer[])) AND (property_floorplan_id = pf.id) AND (lease_start_window_id = 69058))
  • Rows Removed by Filter: 20446
  • Heap Blocks: exact=160265
15. 71.307 1,143.496 ↓ 0.0 0 19

BitmapAnd (cost=2,501.13..2,501.13 rows=1,545 width=0) (actual time=60.184..60.184 rows=0 loops=19)

16. 437.228 437.228 ↓ 12.1 180,374 19

Bitmap Index Scan on pk_cached_applications (cost=0.00..153.31 rows=14,917 width=0) (actual time=23.012..23.012 rows=180,374 loops=19)

  • Index Cond: (cid = pf.cid)
17. 634.961 634.961 ↓ 1.0 232,598 19

Bitmap Index Scan on idx_cached_applications_lease_status_type_id (cost=0.00..2,347.57 rows=231,771 width=0) (actual time=33.419..33.419 rows=232,598 loops=19)

  • Index Cond: (lease_status_type_id = ANY ('{3,4,5}'::integer[]))
18. 0.240 0.240 ↑ 1.0 1 6

Index Scan using pk_space_configurations on space_configurations sc (cost=0.28..2.30 rows=1 width=12) (actual time=0.040..0.040 rows=1 loops=6)

  • Index Cond: ((ca.cid = cid) AND (cid = pf.cid) AND (ca.desired_space_configuration_id = id))
19. 0.054 0.576 ↓ 0.0 0 6

GroupAggregate (cost=0.43..2.46 rows=1 width=12) (actual time=0.096..0.096 rows=0 loops=6)

  • Group Key: lus.lease_id
20. 0.522 0.522 ↑ 1.0 1 6

Index Scan using idx_lease_unit_spaces_lease_id on lease_unit_spaces lus (cost=0.43..2.45 rows=1 width=4) (actual time=0.086..0.087 rows=1 loops=6)

  • Index Cond: (lease_id = ca.lease_id)
  • Filter: ((unit_space_id IS NOT NULL) AND (deleted_by IS NULL) AND (deleted_on IS NULL) AND (cid = ca.cid) AND (property_id = ca.property_id))
  • Rows Removed by Filter: 6
21. 8.018 8.018 ↓ 71.0 71 19

Index Scan using idx_unit_spaces_cid_property_floorplan_id on unit_spaces us (cost=0.42..4.55 rows=1 width=12) (actual time=0.043..0.422 rows=71 loops=19)

  • Index Cond: ((cid = 12924) AND (property_floorplan_id = ut.property_floorplan_id))
  • Filter: ((deleted_by IS NULL) AND (property_id = 221633))
  • Rows Removed by Filter: 1
Planning time : 50.214 ms