explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 9vQ0

Settings
# exclusive inclusive rows x rows loops node
1. 0.001 0.058 ↓ 0.0 0 1

Nested Loop Left Join (cost=189,440.65..189,452.99 rows=1 width=40) (actual time=0.058..0.058 rows=0 loops=1)

  • Join Filter: (tblapplication.applicationid = groups.foreignkey)
  • 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.057 0.057 ↓ 0.0 0 1

Index Scan using tblapplication_pkey on tblapplication (cost=94,720.18..94,722.24 rows=1 width=68) (actual time=0.057..0.057 rows=0 loops=1)

  • Index Cond: (applicationid = 9133012)
  • 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: 1
3.          

SubPlan (for Index Scan)

4. 0.000 0.000 ↓ 0.0 0

Index Scan using tblvacancy_closingdate_idx on tblvacancy (cost=0.42..94,327.10 rows=157,116 width=4) (never executed)

  • Index Cond: ((closingdate <= '2019-09-01'::date) AND (closingdate IS NOT NULL))
  • Filter: (NOT openclosingdate)
5. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.58..10.78 rows=1 width=8) (never executed)

6. 0.000 0.000 ↓ 0.0 0

Index Scan using groups_foreignkey_idx on groups (cost=0.29..2.33 rows=1 width=8) (never executed)

  • Index Cond: ((foreignkey = 9133012) AND ((keytable)::text = 'tblapplication'::text))
7. 0.000 0.000 ↓ 0.0 0

Index Scan using fk_ticket_groupid_autoidx on ticket (cost=0.29..8.45 rows=1 width=8) (never executed)

  • Index Cond: (groupid = groups.groupid)
  • Filter: (tickettype = 'Convictions'::tickettypes)
Planning time : 42.666 ms
Execution time : 0.524 ms