explain.depesz.com

PostgreSQL's explain analyze made readable

Result: gWk3

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 348.627 7,859,995.641 ↓ 49.8 425,805 1

Sort (cost=135,055,529.25..135,055,550.61 rows=8,544 width=274) (actual time=7,859,924.225..7,859,995.641 rows=425,805 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: 102306kB
2. 112.856 7,859,647.014 ↓ 49.8 425,805 1

Hash Join (cost=25,388,932.03..135,054,971.29 rows=8,544 width=274) (actual time=4,751,862.654..7,859,647.014 rows=425,805 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. 0.000 7,859,531.494 ↓ 49.8 425,805 1

Hash Right Join (cost=25,388,774.50..135,054,769.96 rows=8,544 width=239) (actual time=4,751,859.956..7,859,531.494 rows=425,805 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. 1,031.188 3,304,136.949 ↑ 17.8 75,293 1

Gather (cost=6,097,999.21..115,392,718.43 rows=1,337,932 width=114) (actual time=195,725.225..3,304,136.949 rows=75,293 loops=1)

  • Output: e_1.event_datetime, e_1.id, e_1.event_handle, e_1.created_by, e_1.cid, e_1.lease_interval_id
  • Workers Planned: 2
  • Workers Launched: 2
5. 3,138,716.540 3,303,105.761 ↑ 22.2 25,098 3

Parallel Bitmap Heap Scan on public.events e_1 (cost=6,096,999.21..115,257,925.23 rows=557,472 width=114) (actual time=195,713.381..3,303,105.761 rows=25,098 loops=3)

  • Output: e_1.event_datetime, e_1.id, e_1.event_handle, e_1.created_by, e_1.cid, e_1.lease_interval_id
  • Recheck Cond: (e_1.event_type_id = ANY ('{2,4,16,33}'::integer[]))
  • Rows Removed by Index Recheck: 355903720
  • Filter: ((NOT e_1.is_deleted) AND (e_1.ps_product_id = 46) AND ((e_1.event_datetime)::date >= '01/01/2019'::date))
  • Rows Removed by Filter: 194789682
  • Heap Blocks: exact=738125 lossy=18471016
  • Worker 0: actual time=195707.542..3303158.054 rows=25089 loops=1
  • Worker 1: actual time=195707.842..3303159.436 rows=24476 loops=1
6. 164,389.221 164,389.221 ↑ 1.0 584,538,174 1

Bitmap Index Scan on events_event_type_id_idx (cost=0.00..6,096,664.73 rows=584,784,108 width=0) (actual time=164,389.220..164,389.221 rows=584,538,174 loops=1)

  • Index Cond: (e_1.event_type_id = ANY ('{2,4,16,33}'::integer[]))
7. 390.048 4,556,134.627 ↓ 49.4 422,410 1

Hash (cost=19,290,647.13..19,290,647.13 rows=8,544 width=137) (actual time=4,556,134.627..4,556,134.627 rows=422,410 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: 66135kB
8. 2,044.381 4,555,744.579 ↓ 49.4 422,410 1

Nested Loop (cost=23,588.28..19,290,647.13 rows=8,544 width=137) (actual time=105,989.478..4,555,744.579 rows=422,410 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
9. 14.838 14.838 ↑ 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.041..14.838 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
10. 471,229.740 4,553,685.360 ↓ 518.0 518 815

Bitmap Heap Scan on public.events e (cost=23,588.00..23,669.00 rows=1 width=114) (actual time=5,291.554..5,587.344 rows=518 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: 178215
  • Heap Blocks: exact=64863419
11. 23,588.755 4,082,455.620 ↓ 0.0 0 815

BitmapAnd (cost=23,588.00..23,588.00 rows=72 width=0) (actual time=5,009.148..5,009.148 rows=0 loops=815)

12. 33,567.405 33,567.405 ↓ 2.3 178,885 815

Bitmap Index Scan on events_property_id_idx (cost=0.00..829.44 rows=77,094 width=0) (actual time=41.187..41.187 rows=178,885 loops=815)

  • Index Cond: (e.property_id = p.id)
13. 4,025,299.460 4,025,299.460 ↓ 50.2 107,855,645 676

Bitmap Index Scan on events_cid_idx (cost=0.00..22,758.32 rows=2,146,998 width=0) (actual time=5,954.585..5,954.585 rows=107,855,645 loops=676)

  • Index Cond: (e.cid = p.cid)
14. 0.730 2.664 ↓ 1.0 3,980 1

Hash (cost=107.79..107.79 rows=3,979 width=27) (actual time=2.664..2.664 rows=3,980 loops=1)

  • Output: c.company_name, c.id
  • Buckets: 4096 Batches: 1 Memory Usage: 269kB
15. 1.934 1.934 ↓ 1.0 3,980 1

Seq Scan on public.clients c (cost=0.00..107.79 rows=3,979 width=27) (actual time=0.030..1.934 rows=3,980 loops=1)

  • Output: c.company_name, c.id
Planning time : 9.617 ms
Execution time : 7,861,048.365 ms