explain.depesz.com

PostgreSQL's explain analyze made readable

Result: qO2S : Optimization for: Optimization for: Optimization for: Optimization for: plan #qd5H; plan #cAEL; plan #N8Zi; plan #Np0Y

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.000 0.070 ↓ 0.0 0 1

Unique (cost=299.44..299.47 rows=1 width=144) (actual time=0.070..0.070 rows=0 loops=1)

2. 0.037 0.070 ↓ 0.0 0 1

Sort (cost=299.44..299.44 rows=1 width=144) (actual time=0.070..0.070 rows=0 loops=1)

  • Sort Key: e.scheduled_datetime, e.id, st.property_id, st.id, e.lease_interval_id, ca.id, ((st.task_details ->> 'is_enable_manual_contact'::text)), ((st.task_details ->> 'is_enable_email'::text)), ((st.task_details ->> 'is_enable_sms'::text)), (CASE WHEN ((ar.id IS NULL) AND (r.id IS NULL)) THEN 0 ELSE 1 END), (CASE WHEN ((r.enable_portal_access IS TRUE) AND (ast.id <= r.interaction_cutoff_stage_status_id)) THEN 0 ELSE 1 END), ast.id, r.interaction_cutoff_stage_status_id
  • Sort Method: quicksort Memory: 25kB
3. 0.001 0.033 ↓ 0.0 0 1

Nested Loop Left Join (cost=287.85..299.43 rows=1 width=144) (actual time=0.033..0.033 rows=0 loops=1)

  • Filter: (ste.id IS NULL)
4. 0.000 0.032 ↓ 0.0 0 1

Nested Loop (cost=2.14..13.68 rows=1 width=281) (actual time=0.032..0.032 rows=0 loops=1)

5. 0.000 0.032 ↓ 0.0 0 1

Nested Loop Left Join (cost=1.85..12.16 rows=1 width=293) (actual time=0.032..0.032 rows=0 loops=1)

6. 0.001 0.032 ↓ 0.0 0 1

Nested Loop Left Join (cost=1.72..11.52 rows=1 width=301) (actual time=0.032..0.032 rows=0 loops=1)

  • Join Filter: ((r.cid = ar.cid) AND (r.id = ar.referrer_id))
7. 0.000 0.031 ↓ 0.0 0 1

Nested Loop Left Join (cost=1.57..9.34 rows=1 width=300) (actual time=0.031..0.031 rows=0 loops=1)

8. 0.000 0.031 ↓ 0.0 0 1

Nested Loop (cost=1.43..9.14 rows=1 width=288) (actual time=0.031..0.031 rows=0 loops=1)

9. 0.001 0.031 ↓ 0.0 0 1

Nested Loop (cost=1.00..6.67 rows=1 width=268) (actual time=0.031..0.031 rows=0 loops=1)

10. 0.030 0.030 ↓ 0.0 0 1

Index Scan using pk_scheduled_tasks on scheduled_tasks st (cost=0.43..2.50 rows=1 width=301) (actual time=0.030..0.030 rows=0 loops=1)

  • Index Cond: (cid = 235)
  • Filter: ((deleted_by IS NULL) AND (deleted_on IS NULL) AND (scheduled_task_type_id = ANY ('{4,7}'::integer[])) AND (schedule_type_id = 6) AND ((schedule_details ->> 'event_trigger_type'::text) = 'immediate'::text) AND (((task_details ->> 'is_enable_email'::text))::integer = 1) AND ((((task_details ->> 'is_enable_manual_contact'::text))::integer = 1) OR (((task_details ->> 'is_enable_email'::text))::integer = 1) OR (((task_details ->> 'is_enable_sms'::text))::integer = 1)))
11. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_events_cid_etid_pid_event_datetime on events e (cost=0.57..4.17 rows=1 width=56) (never executed)

  • Index Cond: ((cid = 235) AND (event_type_id = ((st.task_details ->> 'mapping_event_type_id'::text))::integer) AND (property_id = st.property_id))
  • Filter: ((NOT is_deleted) AND (NOT is_resident) AND (COALESCE(ps_product_id, 0) <> 14) AND (NOT COALESCE(((details ->> 'do_not_trigger_events'::text))::boolean, false)) AND CASE WHEN ((new_stage_id = 1) OR (new_stage_id IS NULL) OR (event_type_id = 274)) THEN (st.scheduled_task_type_id = 7) ELSE (st.scheduled_task_type_id = 4) END AND CASE WHEN (event_type_id = 274) THEN (((details ->> 'is_send_email'::text))::integer = 1) ELSE true END AND (event_type_id = ANY ('{1,3,7,9,10,13,12,17,78,274,18,66,142,148,11}'::integer[])) AND CASE WHEN (st.default_scheduled_task_id = ANY ('{10,31}'::integer[])) THEN (scheduled_datetime > (now() - '24:00:00'::interval)) ELSE (LEAST(scheduled_datetime, event_datetime) > (now() - '24:00:00'::interval)) END AND CASE WHEN (((st.task_details ->> 'mapping_event_type_id'::text))::integer = 17) THEN (event_datetime > (now() - '24:00:00'::interval)) ELSE (scheduled_datetime > (now() - '24:00:00'::interval)) END AND CASE WHEN (event_type_id = 66) THEN (new_stage_id = ((st.task_details ->> 'mapping_new_stage_id'::text))::integer) WHEN (event_type_id = ANY ('{10,274}'::integer[])) THEN (COALESCE(ps_product_id, 1) = ANY ((btrim((st.schedule_details ->> 'ps_product_ids'::text)))::integer[])) WHEN (event_type_id = 78) THEN (default_event_result_id = ((st.task_details ->> 'mapping_event_result_id'::text))::integer) WHEN (event_type_id = 3) THEN ((COALESCE(event_sub_type_id, 0) = COALESCE(((st.task_details ->> 'mapping_event_sub_type_id'::text))::integer, 0)) AND CASE WHEN ((ps_product_id = ANY ('{46,36}'::integer[])) AND (event_sub_type_id IS NOT NULL)) THEN true ELSE (COALESCE(ps_product_id, 0) = ANY (COALESCE((btrim((st.schedule_details ->> 'ps_product_ids'::text)))::integer[], '{0,36,58}'::integer[]))) END) WHEN (event_type_id = 7) THEN (COALESCE(event_sub_type_id, 0) = COALESCE(((st.task_details ->> 'mapping_event_sub_type_id'::text))::integer, 0)) WHEN (event_type_id = 142) THEN ((ps_product_id = ANY ((btrim((st.schedule_details ->> 'ps_product_ids'::text)))::integer[])) AND (ps_product_id = 46)) WHEN (event_type_id = 17) THEN (COALESCE(ps_product_id, 1) = ANY (COALESCE((btrim((st.schedule_details ->> 'ps_product_ids'::text)))::integer[], '{1}'::integer[]))) WHEN (event_type_id = 148) THEN ((((st.task_details ->> 'mapping_event_type_id'::text))::integer = 148) AND (((st.task_details ->> 'mapping_new_status_id'::text))::integer = 1)) WHEN (event_type_id = ANY ('{12,13}'::integer[])) THEN ((new_status_id = ((st.task_details ->> 'mapping_new_status_id'::text))::integer) AND CASE WHEN ((event_type_id = 12) AND (new_status_id = 1)) THEN (COALESCE(ps_product_id, 1) = ANY ((btrim((st.schedule_details ->> 'ps_product_ids'::text)))::integer[])) ELSE true END) WHEN (event_type_id = 11) THEN ((new_status_id = ((st.task_details ->> 'mapping_new_status_id'::text))::integer) AND (new_status_id = 4) AND (new_stage_id = ((st.task_details ->> 'mapping_new_stage_id'::text))::integer) AND (new_stage_id = 2)) ELSE true END AND (property_id = ANY ('{172775,171695,175646,172800,172028,237044,579269,570828,486794,173366,172290,252337,497328,625012,917667,120793,174075,123450,519273,220477,498132,18462,512312,171712,857025,589259,496815,508115,508159,171707,5627,504319,577852,568904,19463,491748,486792,599214,523309,517471,673163,996382,6559,502725,9764,494345,894671,582347,572287,575506,186596,160681,158414,485382,841809,809363,123082,147909,914940,801834,803335,807930,807933,496437,639428,572176,487974,487972,249119,650661,516602,621472,664460,640326,662364,653735,646114,261586,526716,166336,498148,593913,551064,950666,2652,621470,529405,482451,352235,222221,493277,564478,692210,593899,608344,492139,837745,259871,74929,126714,862268,605343,492174,492173,109885,147902,723388,593944,731189,598093,737320,123086,600259,222074,719721,719131,252185,352772,34933,2515,575656,575651,719868,59927,637568,640328,640753,640750,634234,825359,844092,833290,637570,27600,352774,506048,825355,358965,19457,573919,503577,172377,1030897,260336,207585,207432,206380,207242,84241,353610,353611,353612,503831,526298,526307,526299,526300,526301,526302,526304,526305,526306,2672,506158,821984,822069,531670,204878,194949,506010,124887,124889,125300,252557,676756,2674,247996,14687,6831,20894,221799,221801,221804,172728,2653,47781,185347,10370,493157,508794,70654,622724,325737,230707,265245,484550,2670,94984,94987,530570,530593,530574,530576,530579,530583,530585,530589,530591,232992,186693,490851,506045,506049,506008,262734,634820,488114,484022,484019,181404,22637,1022,557879,267276,267277,490852,2676,794272,2675,641019,494572,18855,577973,326071,360337,24785,676266,564089,952789,568872,678596,483208,359712,351290,21890,109883,189303,123094,545906,545949,324612,495467,536101,123869,324579,219257,359684,580483,538978,23844,2662,9611,633119,633166,1021719,1017386,267216,872474,872559,872005,520619,329353,202722,172218,172222,531608,1024315,696235,580684,701876,582312,1027809,705323,692576,578416,352771,58038,524950,167948,526319,177307,490849,504341,237919,493282,493286,493283,97995,97997,490657,352773,352769,185774,497280,98014,359639,599234,599235,599236,599237,634230,626405,692606,682741,630272,631014,186563,517176,156792,48444,582050,580484,198209,126847,508053,2654,747022,352222,492095,719863,809469,725625,551175,551179,551257,497637,491910,526303,172165,2657,352770,2668,351450,185793,185789,503938,626591,787782,32960,95467,172798,87138,593900,593915,643452,688467,691463,968577,334780,486226,486224,39384,490660,192855,506009,530587,2673,172096,172067,54638,93148,172050,28782,628784,801883,809463,814189,630991,787786,621475,626594,614742,615608,538993,101518,35973,503283,99427,506150,506147,534196,175934,123402,9801,41953,534122,534125,493107,486797,493288,673187,494505,329453,590192,169457,101516,353527,174879,241379,207049,207050,539085,228025,220701,329455,627065,649226,1017392,32054,939709,550455,633157,83906,696930,493274,508939,572277,572291,572286,87291,87295,87294,240962,171700,51446,191648,29287,709397,587292,713949,589895,591517,718250,506811,555309,179997,601791,755903,604653,763849,608363,771826,763842,613886,745858,519649,582303,614740,673884,613890,578415,88508,589893,598095,591519,587290,584849,557881,588071,601796,608360,43637,527335,621295,358365,508874,872498,495654,507122,517450,602274,233684,30660,531368,35912,29078,539274,2667,2663,248001,220653,123850,229336,229421,220655,844185,91495,91496,522859,265829,328344,181803,189071,110108,71278,101781,74930,525562,325890,325904,601786,872964,575512,952991,712148,710122,506712,325888,502563,1015724,718297,1029207,502566,1034510,807664,723129,509077,862412,506705,519391,519393,31660,860511,639146,502553,246527,252187,252833,677186,358976,506012,351677,908537,667823,917524,673161,490400,119745,90865,650676,881652,653738,889094,889089,889088,889090,889087,662380,664458,872400,492993,641162,641164,33718,104844,1025974,126849,352233,719652,6947,591473,325733,252363,104522,506708,2770,262758,621545,185769,230705,142871,352931,2659,13646,640963,86783,86893,640174,584412,249228,584669,31512,33431,2535,48453,628775,70738,534123,359708,668848,187101,185799,486333,192853,205119,218157,218177,205121,206379,587008,814582,581789,207433,208279,218546,205122,208371,326730,186943,186712,186709,186698,186697,186691,326729,326731,326732,326733,326920,503581,503582,508497,518142,518143,233244,194304,256845,491872,497227,353616,545864,591426,506738,192980,204431,90454,90452,126924,198210,249132,171464,168274,171708,598840,545842,493290,593917,593922,593926,593930,667806,2660,247947,1032996,718252,598117,551037,601788,626670,626672,519650,92097,48890,493245,569221,233215,207434,91140,227666,352775,252182,542059,485558,577850,37908,352266,2665,985772,601860,630110,352237,640176,696240,93115,84240,16534,592286,592287,335127,174878,633262,530596,241775,241782,241784,705378,642276,352224,858070,643448,646152,640961,50783,119750,353648,356952,519397,952579,719130,719720,718973,32905,357116,719654,556444,560818,558036,545975,535849,551171,599222,839262,828743,858136,820068,556688,44903,260339,24695,5316,518065,124885,190151,208219,964848,202082,265242,505002,844108,352231,599246,599247,599248,599249,225351,2664,15186,543024,634712,641084,551451,484026,484024,484028,508075,665223,262508,2712,14673,577689,577691,570423,1030775,487970,490843,517630,110321,964852,881640,83780,99888,525647,352361,147921,820985,168556,162731,171473,93096,126709,123907,592228,489513,489594,489592,140389,893275,93112,140402,161824,161826,232993,218179,643920,101265,101258,506706,506725,506726,517534,123905,889104,889110,889113,889093,889102,889101,889100,889097,889096,889095,95565,95572,894891,81818,671047,229293,575440,521869,125395,538995,221529,525598,1004096,160842,147868,93338,93340,93342,98281,176168,506743,705838,724716,515024,95498,536670,483574,171846,237058,171681,142800,590207,91464,259865,171390,531650,330638,577848,570891,119541,587028,591570,641230,845009,193108,545682,233382,250457,42938,42939,152804,175696,1034561,557912,110234,598452,506741,84138,84145,328641,327901,185263,27085,62593,62598,354228,252232,252234,252236,204678,174174,326352,2010,63092,526098,597126,19461,64433,26550,35272,14692,706703,2537,560906,553557,506007,506046,506047,506050,506011,506043,506013,506042,506044,598296,265240,545951,84085,621528,8376,550454,2209,53428,101756,104835,642711,649565,988178,988185,996449,99295,550592,504774,1028872,227101,172167,262733,517628,233682,233392,502751,247997,171359,68952,31238,593955,719642,719640,490850,172166,192490,487975,185843,181680,542056,181709,487012,507189,872004,359699,359701,101520,186671,205120,233243,218178,186653,186651,20822,484447,158441,158444,158505,158508,158510,158512,158524,1029228,201472,200961,572208,181583,504987,504986,582355,508707,523890,911411}'::integer[])))
12. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_cached_applications_lease_interval_id on cached_applications ca (cost=0.43..2.46 rows=1 width=28) (never executed)

  • Index Cond: (lease_interval_id = e.lease_interval_id)
  • Filter: ((NOT is_deleted) AND (application_status_id <> ALL ('{5,6}'::integer[])) AND (cid = 235) AND (lease_interval_type_id = 1) AND (e.property_id = property_id))
13. 0.000 0.000 ↓ 0.0 0

Index Scan using pk_application_referrers_id on application_referrers ar (cost=0.15..0.20 rows=1 width=16) (never executed)

  • Index Cond: ((cid = ca.cid) AND (cid = 235))
  • Filter: ((deleted_by IS NULL) AND (deleted_on IS NULL) AND (application_id = ca.id))
14. 0.000 0.000 ↓ 0.0 0

Index Scan using pk_referrers_id on referrers r (cost=0.14..2.16 rows=1 width=13) (never executed)

  • Index Cond: (cid = 235)
  • Filter: ((deleted_by IS NULL) AND (deleted_on IS NULL))
15. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_application_stage_status_lease_interval_type_id on application_stage_statuses ast (cost=0.14..0.63 rows=1 width=16) (never executed)

  • Index Cond: ((lease_interval_type_id = ca.lease_interval_type_id) AND (lease_interval_type_id = 1))
  • Filter: ((application_stage_id = ca.application_stage_id) AND (application_status_id = ca.application_status_id))
16. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_properties_id on properties p (cost=0.29..1.17 rows=1 width=8) (never executed)

  • Index Cond: (id = ca.property_id)
  • Filter: ((termination_date IS NULL) AND (is_disabled <> 1) AND (cid = 235))
17. 0.000 0.000 ↓ 0.0 0

Limit (cost=285.71..285.71 rows=1 width=4) (never executed)

18. 0.000 0.000 ↓ 0.0 0

Unique (cost=285.71..285.71 rows=1 width=4) (never executed)

19. 0.000 0.000 ↓ 0.0 0

Sort (cost=285.71..285.71 rows=1 width=4) (never executed)

  • Sort Key: ste.id
20. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on scheduled_task_executions ste (cost=282.64..285.70 rows=1 width=4) (never executed)

  • Recheck Cond: ((event_id = e.id) AND (scheduled_task_action = 'Email'::text))
  • Filter: ((cid = st.cid) AND (scheduled_task_id = st.id))
21. 0.000 0.000 ↓ 0.0 0

BitmapAnd (cost=282.64..282.64 rows=3 width=0) (never executed)

22. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on idx_scheduled_task_executions_event_id (cost=0.00..35.91 rows=3,397 width=0) (never executed)

  • Index Cond: (event_id = e.id)
23. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on idx_scheduled_task_executions_scheduled_task_action (cost=0.00..246.47 rows=18,121 width=0) (never executed)

  • Index Cond: (scheduled_task_action = 'Email'::text)