explain.depesz.com

PostgreSQL's explain analyze made readable

Result: quxb

Settings
# exclusive inclusive rows x rows loops node
1. 243.460 1,011.578 ↓ 264.0 264 1

Nested Loop Left Join (cost=189,440.65..191,793.26 rows=1 width=40) (actual time=748.796..1,011.578 rows=264 loops=1)

  • Filter: (((tblapplication.status = 100) AND (tblapplication.completeddatetime <= '2019-06-02 00:59:59+01'::timestamp with time zone) AND (tblapplication.completeddatetime IS NOT NULL) AND ((COALESCE(tblapplication.actualstartdate, tblapplication.bookedstartdate, tblapplication.offerstartdate, tblapplication.offerproposedstartdate, (tblapplication.crbebulkresponsedatetime)::date) IS NULL) OR (COALESCE(tblapplication.actualstartdate, tblapplication.bookedstartdate, tblapplication.offerstartdate, tblapplication.offerproposedstartdate, (tblapplication.crbebulkresponsedatetime)::date) <= '2019-05-14'::date)) AND ((tblapplication.crbformtype <> 'Electronic'::nn_crbformtype) OR (((tblapplication.crbsenttoebulkdatetime < '2019-06-02 00:59:59+01'::timestamp with time zone) OR (tblapplication.crbsenttoebulkdatetime IS NULL) OR (ticket.state <> 'WithCRB'::ticketstates)) AND ((ticket.state <> ALL ('{DisclosureAwaitingChecking,AwaitCertificate,CertificateUnderReview,QueriedByCRB}'::ticketstates[])) OR (COALESCE(tblapplication.actualstartdate, tblapplication.bookedstartdate, tblapplication.offerstartdate, tblapplication.offerproposedstartdate, (tblapplication.crbebulkresponsedatetime)::date) IS NULL) OR (COALESCE(tblapplication.actualstartdate, tblapplication.bookedstartdate, tblapplication.offerstartdate, tblapplication.offerproposedstartdate, (tblapplication.crbebulkresponsedatetime)::date) < '2019-05-14'::date))))) OR ((tblapplication.status <> 100) AND (tblapplication.starteddatetime <= '2019-06-02 00:59:59+01'::timestamp with time zone) AND (tblapplication.starteddatetime IS NOT NULL)) OR ((tblapplication.status = 0) AND (hashed SubPlan 1)))
2. 0.000 764.686 ↓ 264.0 264 1

Index Scan using tblapplication_pkey on tblapplication (cost=94,720.18..97,070.28 rows=1 width=68) (actual time=748.600..764.686 rows=264 loops=1)

  • Filter: ((expecteddeletiondate IS NULL) AND (((status = 100) AND (completeddatetime <= '2019-06-02 00:59:59+01'::timestamp with time zone) AND (completeddatetime IS NOT NULL) AND ((COALESCE(actualstartdate, bookedstartdate, offerstartdate, offerproposedstartdate, (crbebulkresponsedatetime)::date) IS NULL) OR (COALESCE(actualstartdate, bookedstartdate, offerstartdate, offerproposedstartdate, (crbebulkresponsedatetime)::date) <= '2019-05-14'::date))) OR ((status <> 100) AND (starteddatetime <= '2019-06-02 00:59:59+01'::timestamp with time zone) AND (starteddatetime IS NOT NULL)) OR ((status = 0) AND (hashed SubPlan 1))))
  • Rows Removed by Filter: 23277
3.          

SubPlan (for Index Scan)

4. 836.544 836.544 ↑ 1.1 142,876 2

Index Scan using tblvacancy_closingdate_idx on tblvacancy (cost=0.42..94,327.10 rows=157,116 width=4) (actual time=0.214..418.272 rows=142,876 loops=2)

  • Index Cond: ((closingdate <= '2019-09-01'::date) AND (closingdate IS NOT NULL))
  • Filter: (NOT openclosingdate)
  • Rows Removed by Filter: 20
5. 0.318 3.432 ↑ 1.0 1 264

Nested Loop (cost=0.58..3.02 rows=1 width=8) (actual time=0.008..0.013 rows=1 loops=264)

6. 0.792 0.792 ↑ 1.0 1 264

Index Scan using groups_foreignkey_idx on groups (cost=0.29..2.33 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=264)

  • Index Cond: ((tblapplication.applicationid = foreignkey) AND ((keytable)::text = 'tblapplication'::text))
7. 2.322 2.322 ↑ 1.0 1 258

Index Scan using fk_ticket_groupid_autoidx on ticket (cost=0.29..0.68 rows=1 width=8) (actual time=0.005..0.009 rows=1 loops=258)

  • Index Cond: (groupid = groups.groupid)
  • Filter: (tickettype = 'Convictions'::tickettypes)
  • Rows Removed by Filter: 8
Planning time : 30.923 ms
Execution time : 1,015.006 ms