explain.depesz.com

PostgreSQL's explain analyze made readable

Result: nSi27 : Optimization for: plan #ze6w

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.002 0.789 ↑ 4.2 6 1

Limit (cost=1.14..297.38 rows=25 width=232) (actual time=0.688..0.789 rows=6 loops=1)

  • Output: (enumtostring('MessageStatusEnum'::character varying, m."Status")), m0."NextAttemptDate", (enumtostring('MessageTypeEnum'::character varying, m."MessageType")), (CASE WHEN (m0."ID" IS NULL) THEN m."NumAttempts" ELSE m0."NumAttempts" END), ((d."ID" IS NULL)), d."Code", d."Name", (enumtostring('ReceiverTypeEnum'::character varying, m."SendToReceiverType")), m."SendToAddress", t0."Name", m."ID", m."CreatedDate", m."DeliveryDate
  • Buffers: shared hit=35 read=1
2. 0.660 0.787 ↑ 4,546.2 6 1

Nested Loop Left Join (cost=1.14..323,228.41 rows=27,277 width=232) (actual time=0.687..0.787 rows=6 loops=1)

  • Output: enumtostring('MessageStatusEnum'::character varying, m."Status"), m0."NextAttemptDate", enumtostring('MessageTypeEnum'::character varying, m."MessageType"), CASE WHEN (m0."ID" IS NULL) THEN m."NumAttempts" ELSE m0."NumAttempts" END, (d."ID" IS NULL), d."Code", d."Name", enumtostring('ReceiverTypeEnum'::character varying, m."SendToReceiverType"), m."SendToAddress", t0."Name", m."ID", m."CreatedDate", m."DeliveryDate
  • Inner Unique: true
  • Join Filter: (t."TicketFunctionID" = t0."ID")
  • Rows Removed by Join Filter: 16
  • Buffers: shared hit=35 read=1
3. 0.005 0.115 ↑ 4,546.2 6 1

Nested Loop Left Join (cost=1.14..299,814.59 rows=27,277 width=189) (actual time=0.041..0.115 rows=6 loops=1)

  • Output: m."Status", m."MessageType", m."NumAttempts", m."SendToReceiverType", m."SendToAddress", m."ID", m."CreatedDate", m."DeliveryDate", m0."NextAttemptDate", m0."ID", m0."NumAttempts", d."ID", d."Code", d."Name", t."TicketFunctionID
  • Inner Unique: true
  • Buffers: shared hit=34 read=1
4. 0.006 0.098 ↑ 4,546.2 6 1

Nested Loop Left Join (cost=0.71..157,344.52 rows=27,277 width=189) (actual time=0.037..0.098 rows=6 loops=1)

  • Output: m."Status", m."MessageType", m."NumAttempts", m."SendToReceiverType", m."SendToAddress", m."ID", m."CreatedDate", m."DeliveryDate", m."TicketID", m0."NextAttemptDate", m0."ID", m0."NumAttempts", d."ID", d."Code", d."Name
  • Inner Unique: true
  • Buffers: shared hit=18 read=1
5. 0.009 0.086 ↑ 4,546.2 6 1

Nested Loop Left Join (cost=0.43..55,291.34 rows=27,277 width=141) (actual time=0.033..0.086 rows=6 loops=1)

  • Output: m."Status", m."MessageType", m."NumAttempts", m."SendToReceiverType", m."SendToAddress", m."ID", m."CreatedDate", m."DeliveryDate", m."SendToDestinationID", m."TicketID", m0."NextAttemptDate", m0."ID", m0."NumAttempts
  • Inner Unique: true
  • Join Filter: (m."ID" = m0."MessageID")
  • Rows Removed by Join Filter: 66
  • Buffers: shared hit=9 read=1
6. 0.053 0.053 ↑ 4,546.2 6 1

Index Scan Backward using "IX_Messages_DeliveryDateStatus" on public."Messages" m (cost=0.43..50,789.50 rows=27,277 width=113) (actual time=0.007..0.053 rows=6 loops=1)

  • Output: m."ID", m."LastModifyDateTime", m."OneCallCenterID", m."Status", m."MessageType", m."Source", m."CreatedDate", m."Content", m."SendToDestinationID", m."TicketID", m."SendToAddress", m."SendToReceiverType", m."DeliveryDate", m."NumAttempts", m."ExternalMessageID", m."ExternalProtocol", m."BroadcastMessageID", m."RecipientPersonID
  • Index Cond: ((m."DeliveryDate" >= '2020-07-21 00:00:00-04'::timestamp with time zone) AND (m."DeliveryDate" < '2020-07-27 00:00:00-04'::timestamp with time zone) AND (m."Status" = 1))
  • Buffers: shared hit=8 read=1
7. 0.009 0.024 ↑ 1.0 11 6

Materialize (cost=0.00..1.17 rows=11 width=44) (actual time=0.003..0.004 rows=11 loops=6)

  • Output: m0."NextAttemptDate", m0."ID", m0."NumAttempts", m0."MessageID
  • Buffers: shared hit=1
8. 0.015 0.015 ↑ 1.0 11 1

Seq Scan on public."MessageQueue" m0 (cost=0.00..1.11 rows=11 width=44) (actual time=0.013..0.015 rows=11 loops=1)

  • Output: m0."NextAttemptDate", m0."ID", m0."NumAttempts", m0."MessageID
  • Buffers: shared hit=1
9. 0.006 0.006 ↑ 1.0 1 6

Index Scan using "PK_Destinations" on public."Destinations" d (cost=0.28..3.74 rows=1 width=64) (actual time=0.001..0.001 rows=1 loops=6)

  • Output: d."ID", d."Code", d."IsActive", d."IsDeleted", d."LastModifyDateTime", d."Name", d."OneCallCenterID", d."CurrentDeviceID", d."MemberID", d."SendEnabled", d."SendSingleCopy", d."ServiceProviderID", d."EODAuditMessageFormatID", d."TicketMessageFormatID
  • Index Cond: (d."ID" = m."SendToDestinationID")
  • Buffers: shared hit=9
10. 0.012 0.012 ↑ 1.0 1 6

Index Scan using "PK_Tickets" on public."Tickets" t (cost=0.42..5.22 rows=1 width=32) (actual time=0.002..0.002 rows=1 loops=6)

  • Output: t."ID", t."AgentPersonID", t."ExpiresDate", t."LastModifyDateTime", t."OneCallCenterID", t."Status", t."TakenStartDate", t."TicketNumber", t."TicketTypeID", t."Version", t."WorkStartDate", t."LocateTypeID", t."ParentTicketID", t."TicketLinkID", t."IsCanceled", t."ActualGeocodeType", t."TakenEndDate", t."IsDamage", t."TicketFunctionID", t."ResponseDueDate", t."DateCalcSeedDate", t."CreateSource", t."FormID", t."ChildTicketNumber", t."ParentTicketNumber", t."IsMeetRequested", t."DigSiteBounds", t."DigSiteCentroid", t."LockedByPersonID", t."LockedDate", t."QAStatus", t."CopyOfTicketNumber", t."ReferenceTicketNumber", t."IsWorkComplete
  • Index Cond: (t."ID" = m."TicketID")
  • Buffers: shared hit=16
11. 0.003 0.012 ↑ 2.7 3 6

Materialize (cost=0.00..1.12 rows=8 width=26) (actual time=0.002..0.002 rows=3 loops=6)

  • Output: t0."Name", t0."ID
  • Buffers: shared hit=1
12. 0.009 0.009 ↑ 1.0 8 1

Seq Scan on "Configuration"."TicketFunctions" t0 (cost=0.00..1.08 rows=8 width=26) (actual time=0.008..0.009 rows=8 loops=1)

  • Output: t0."Name", t0."ID
  • Buffers: shared hit=1
Planning time : 7.003 ms
Execution time : 0.840 ms