explain.depesz.com

PostgreSQL's explain analyze made readable

Result: uzex

Settings
# exclusive inclusive rows x rows loops node
1. 355.613 4,911,675.269 ↓ 50.0 426,975 1

Sort (cost=29,716,116.82..29,716,138.18 rows=8,545 width=274) (actual time=4,911,602.120..4,911,675.269 rows=426,975 loops=1)

  • Output: e.id, e.event_datetime, e_1.event_datetime, ((e_1.event_datetime - e.event_datetime)), e.cid, c.company_name, e.property_id, p.property_name, e.event_handle, e_1.id, e_1.event_handle, e_1.created_by
  • Sort Key: e.event_datetime, e.id
  • Sort Method: quicksort Memory: 102460kB
2. 103.319 4,911,319.656 ↓ 50.0 426,975 1

Hash Join (cost=25,039,920.92..29,715,558.80 rows=8,545 width=274) (actual time=4,630,432.174..4,911,319.656 rows=426,975 loops=1)

  • Output: e.id, e.event_datetime, e_1.event_datetime, (e_1.event_datetime - e.event_datetime), e.cid, c.company_name, e.property_id, p.property_name, e.event_handle, e_1.id, e_1.event_handle, e_1.created_by
  • Inner Unique: true
  • Hash Cond: (e.cid = c.id)
3. 287.282 4,911,207.852 ↓ 50.0 426,975 1

Hash Right Join (cost=25,039,762.28..29,715,356.34 rows=8,545 width=239) (actual time=4,630,423.661..4,911,207.852 rows=426,975 loops=1)

  • Output: e.id, e.event_datetime, e.cid, e.property_id, e.event_handle, e_1.event_datetime, e_1.id, e_1.event_handle, e_1.created_by, p.property_name, p.cid
  • Hash Cond: ((e_1.cid = e.cid) AND (e_1.lease_interval_id = e.lease_interval_id))
4. 282,198.439 446,481.655 ↑ 18.0 75,868 1

Bitmap Heap Scan on public.events e_1 (cost=6,275,320.83..10,728,424.17 rows=1,369,173 width=114) (actual time=165,984.653..446,481.655 rows=75,868 loops=1)

  • Output: e_1.id, e_1.cid, e_1.property_id, e_1.property_unit_id, e_1.unit_space_id, e_1.event_type_id, e_1.event_sub_type_id, e_1.event_result_id, e_1.default_event_result_id, e_1.associated_event_id, e_1.ps_product_id, e_1.old_stage_id, e_1.new_stage_id, e_1.old_status_id, e_1.new_status_id, e_1.company_employee_id, e_1.data_reference_id, e_1.integration_result_id, e_1.lease_id, e_1.lease_interval_id, e_1.customer_id, e_1.scheduled_task_id, e_1.remote_primary_key, e_1.calendar_event_key, e_1.scheduled_datetime, e_1.scheduled_end_datetime, e_1.event_datetime, e_1.event_handle, e_1.title, e_1.notes, e_1.details, e_1.ip_address, e_1.do_not_export, e_1.is_resident, e_1.is_deleted, e_1.updated_by, e_1.updated_on, e_1.created_by, e_1.created_on, e_1.organization_contract_id
  • Recheck Cond: ((e_1.ps_product_id = 46) AND (e_1.event_type_id = ANY ('{2,4,16,33}'::integer[])))
  • Rows Removed by Index Recheck: 138621270
  • Filter: ((NOT e_1.is_deleted) AND ((e_1.event_datetime)::date >= '01/01/2019'::date))
  • Rows Removed by Filter: 246553
  • Heap Blocks: exact=2520233 lossy=4588284
5. 1,069.504 164,283.216 ↓ 0.0 0 1

BitmapAnd (cost=6,275,320.83..6,275,320.83 rows=4,116,612 width=0) (actual time=164,283.216..164,283.216 rows=0 loops=1)

6. 5,417.851 5,417.851 ↓ 1.0 16,126,774 1

Bitmap Index Scan on events_ps_product_id_idx (cost=0.00..169,327.94 rows=16,102,675 width=0) (actual time=5,417.851..5,417.851 rows=16,126,774 loops=1)

  • Index Cond: (e_1.ps_product_id = 46)
7. 157,795.861 157,795.861 ↑ 1.0 585,060,658 1

Bitmap Index Scan on events_event_type_id_idx (cost=0.00..6,105,308.05 rows=585,577,804 width=0) (actual time=157,795.861..157,795.861 rows=585,060,658 loops=1)

  • Index Cond: (e_1.event_type_id = ANY ('{2,4,16,33}'::integer[]))
8. 358.250 4,464,438.915 ↓ 49.6 423,518 1

Hash (cost=18,764,313.27..18,764,313.27 rows=8,545 width=137) (actual time=4,464,438.915..4,464,438.915 rows=423,518 loops=1)

  • Output: e.id, e.event_datetime, e.cid, e.property_id, e.event_handle, e.lease_interval_id, p.property_name, p.cid
  • Buckets: 524288 (originally 16384) Batches: 1 (originally 1) Memory Usage: 66222kB
9. 2,044.664 4,464,080.665 ↓ 49.6 423,518 1

Nested Loop (cost=22,944.72..18,764,313.27 rows=8,545 width=137) (actual time=108,141.774..4,464,080.665 rows=423,518 loops=1)

  • Output: e.id, e.event_datetime, e.cid, e.property_id, e.event_handle, e.lease_interval_id, p.property_name, p.cid
10. 16.951 16.951 ↑ 1.0 815 1

Index Scan using pk_properties on public.properties p (cost=0.28..405.26 rows=815 width=27) (actual time=0.477..16.951 rows=815 loops=1)

  • Output: p.id, p.cid, p.property_id, p.property_type_id, p.company_region_id, p.time_zone_id, p.owner_id, p.account_id, p.occupancy_type_ids, p.remote_primary_key, p.property_name, p.lookup_code, p.country_code, p.vaultware_number, p.min_rent, p.max_rent, p.min_square_feet, p.max_square_feet, p.min_bedrooms, p.max_bedrooms, p.min_bathrooms, p.max_bathrooms, p.number_of_units, p.year_built, p.year_remodeled, p.short_description, p.full_description, p.driving_directions, p.termination_reason, p.termination_date, p.details, p.allows_cats, p.allows_dogs, p.has_availability, p.is_disabled, p.is_test, p.is_managerial, p.order_num, p.imported_on, p.disabled_on, p.updated_by, p.updated_on, p.created_by, p.created_on, p.locale_code, p.default_occupancy_type_id
11. 438,994.860 4,462,019.050 ↓ 520.0 520 815

Bitmap Heap Scan on public.events e (cost=22,944.45..23,023.19 rows=1 width=114) (actual time=5,207.577..5,474.870 rows=520 loops=815)

  • Output: e.id, e.cid, e.property_id, e.property_unit_id, e.unit_space_id, e.event_type_id, e.event_sub_type_id, e.event_result_id, e.default_event_result_id, e.associated_event_id, e.ps_product_id, e.old_stage_id, e.new_stage_id, e.old_status_id, e.new_status_id, e.company_employee_id, e.data_reference_id, e.integration_result_id, e.lease_id, e.lease_interval_id, e.customer_id, e.scheduled_task_id, e.remote_primary_key, e.calendar_event_key, e.scheduled_datetime, e.scheduled_end_datetime, e.event_datetime, e.event_handle, e.title, e.notes, e.details, e.ip_address, e.do_not_export, e.is_resident, e.is_deleted, e.updated_by, e.updated_on, e.created_by, e.created_on, e.organization_contract_id
  • Recheck Cond: ((e.property_id = p.id) AND (e.cid = p.cid))
  • Filter: ((NOT e.is_deleted) AND (e.ps_product_id <> 46) AND (e.event_type_id = 10) AND ((e.event_datetime)::date >= '01/01/2019'::date))
  • Rows Removed by Filter: 178414
  • Heap Blocks: exact=64941133
12. 23,741.668 4,023,024.190 ↓ 0.0 0 815

BitmapAnd (cost=22,944.45..22,944.45 rows=70 width=0) (actual time=4,936.226..4,936.226 rows=0 loops=815)

13. 33,811.090 33,811.090 ↓ 2.3 179,094 815

Bitmap Index Scan on events_property_id_idx (cost=0.00..829.80 rows=77,142 width=0) (actual time=41.486..41.486 rows=179,094 loops=815)

  • Index Cond: (e.property_id = p.id)
14. 3,965,471.432 3,965,471.432 ↓ 51.8 107,956,999 676

Bitmap Index Scan on events_cid_idx (cost=0.00..22,114.39 rows=2,086,125 width=0) (actual time=5,866.082..5,866.082 rows=107,956,999 loops=676)

  • Index Cond: (e.cid = p.cid)
15. 0.814 8.485 ↑ 1.0 3,984 1

Hash (cost=108.84..108.84 rows=3,984 width=27) (actual time=8.485..8.485 rows=3,984 loops=1)

  • Output: c.company_name, c.id
  • Buckets: 4096 Batches: 1 Memory Usage: 269kB
16. 7.671 7.671 ↑ 1.0 3,984 1

Seq Scan on public.clients c (cost=0.00..108.84 rows=3,984 width=27) (actual time=0.410..7.671 rows=3,984 loops=1)

  • Output: c.company_name, c.id
Planning time : 19.376 ms
Execution time : 4,911,744.139 ms