explain.depesz.com

PostgreSQL's explain analyze made readable

Result: GRkU : info

Settings
# exclusive inclusive rows x rows loops node
1. 0.025 64.181 ↓ 2.0 2 1

Hash Join (cost=28,230.80..28,232.08 rows=1 width=399) (actual time=64.150..64.181 rows=2 loops=1)

  • Hash Cond: (("Notification21"."notificationSeqNo")::integer = ("UserNotification9"."notificationSeqNo")::integer)
2. 0.402 64.094 ↓ 2.0 114 1

HashAggregate (cost=28,230.43..28,230.99 rows=56 width=346) (actual time=64.068..64.094 rows=114 loops=1)

  • Group Key: "Notification21".body, "Notification21".subject, "Notification21".xmin, "Notification21"."caseNo", "Notification21"."notificationSeqNo", "Notification21"."countyNo", (("Notification21".ctid)::character varying), "Notification21".status, "Notification21"."caseType", "Notification21"."timestampSent
3. 0.028 63.692 ↓ 2.0 114 1

Append (cost=0.00..28,229.03 rows=56 width=346) (actual time=20.065..63.692 rows=114 loops=1)

4. 7.967 58.474 ↓ 2.2 114 1

Seq Scan on "Notification" "Notification21" (cost=0.00..27,921.03 rows=52 width=785) (actual time=20.064..58.474 rows=114 loops=1)

  • Filter: ((("timestampSent")::timestamp with time zone > '2019-07-08 00:00:00-05'::timestamp with time zone) AND ((NOT (alternatives: SubPlan 3 or hashed SubPlan 4)) OR (("caseNo" IS NOT NULL) AND ("countyNo" IS NOT NULL) AND ('2019-11-05 13:38:18.003-06'::timestamp with time zone > ("timestampSent")::timestamp with time zone) AND (alternatives: SubPlan 5 or hashed SubPlan 6))) AND (("countyNo" IS NULL) OR ("caseNo" IS NULL) OR (alternatives: SubPlan 1 or hashed SubPlan 2)))
  • Rows Removed by Filter: 25
5.          

SubPlan (for Seq Scan)

6. 0.000 0.000 ↓ 0.0 0

Index Only Scan using "NotificationDoc_pkey" on "NotificationDoc" "NotificationDoc27" (cost=0.14..0.36 rows=1 width=0) (never executed)

  • Index Cond: ("notificationSeqNo" = ("Notification21"."notificationSeqNo")::integer)
  • Heap Fetches: 0
7. 0.013 0.013 ↓ 1.1 56 1

Seq Scan on "NotificationDoc" "NotificationDoc27_1" (cost=0.00..0.61 rows=51 width=4) (actual time=0.010..0.013 rows=56 loops=1)

8. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=100.59..101.66 rows=16 width=0) (never executed)

9. 0.000 0.000 ↓ 0.0 0

Index Only Scan using "NotificationDoc_pkey" on "NotificationDoc" "NotificationDoc30" (cost=0.14..0.36 rows=1 width=8) (never executed)

  • Index Cond: ("notificationSeqNo" = ("Notification21"."notificationSeqNo")::integer)
  • Heap Fetches: 0
10. 0.000 0.000 ↓ 0.0 0

Foreign Scan on "DocImage" "DocImage31" (cost=100.45..101.29 rows=1 width=6) (never executed)

11. 3.823 18.904 ↑ 28.4 28 1

Nested Loop (cost=100.45..5,166.91 rows=795 width=4) (actual time=4.085..18.904 rows=28 loops=1)

12. 0.017 0.017 ↓ 1.1 56 1

Seq Scan on "NotificationDoc" "NotificationDoc30_1" (cost=0.00..0.61 rows=51 width=12) (actual time=0.002..0.017 rows=56 loops=1)

13. 15.064 15.064 ↓ 0.0 0 56

Foreign Scan on "DocImage" "DocImage31_1" (cost=100.45..101.29 rows=1 width=6) (actual time=0.269..0.269 rows=0 loops=56)

14. 31.590 31.590 ↑ 1.0 1 117

Foreign Scan on "Case" "Case24" (cost=100.45..101.28 rows=1 width=32) (actual time=0.270..0.270 rows=1 loops=117)

15. 0.000 0.000 ↓ 0.0 0

Foreign Scan on "Case" "Case24_1" (cost=100.00..8,467.37 rows=261,289 width=36) (never executed)

16. 0.064 5.190 ↓ 0.0 0 1

Index Scan using "Notification_timestampSent_idx" on "Notification" "Notification43" (cost=306.54..307.16 rows=4 width=785) (actual time=5.190..5.190 rows=0 loops=1)

  • Filter: (("countyNo" IS NOT NULL) AND ("caseNo" IS NOT NULL) AND (NOT (hashed SubPlan 7)) AND ((status)::text = 'U'::text))
  • Rows Removed by Filter: 18
17.          

SubPlan (for Index Scan)

18. 0.027 5.126 ↓ 2.6 18 1

GroupAggregate (cost=101.40..306.39 rows=7 width=4) (actual time=0.596..5.126 rows=18 loops=1)

  • Group Key: "Notification45"."notificationSeqNo
  • Filter: (count("NotificationDoc46"."docId") <> count("DocImage47"."docId"))
19. 0.981 5.099 ↑ 2.6 18 1

Nested Loop Left Join (cost=101.40..305.95 rows=47 width=12) (actual time=0.301..5.099 rows=18 loops=1)

20. 0.020 0.086 ↓ 6.0 18 1

Merge Join (cost=0.95..2.05 rows=3 width=12) (actual time=0.049..0.086 rows=18 loops=1)

  • Merge Cond: (("NotificationDoc46"."notificationSeqNo")::integer = ("Notification45"."notificationSeqNo")::integer)
21. 0.036 0.036 ↓ 1.1 56 1

Index Only Scan using "NotificationDoc_pkey" on "NotificationDoc" "NotificationDoc46" (cost=0.14..1.21 rows=51 width=12) (actual time=0.011..0.036 rows=56 loops=1)

  • Heap Fetches: 56
22. 0.018 0.030 ↓ 2.6 18 1

Sort (cost=0.81..0.83 rows=7 width=4) (actual time=0.026..0.030 rows=18 loops=1)

  • Sort Key: "Notification45"."notificationSeqNo
  • Sort Method: quicksort Memory: 25kB
23. 0.012 0.012 ↓ 2.6 18 1

Index Scan using "Notification_timestampSent_idx" on "Notification" "Notification45" (cost=0.13..0.72 rows=7 width=4) (actual time=0.006..0.012 rows=18 loops=1)

  • Filter: ((status)::text = 'U'::text)
24. 4.032 4.032 ↓ 0.0 0 18

Foreign Scan on "DocImage" "DocImage47" (cost=100.45..101.29 rows=1 width=6) (actual time=0.224..0.224 rows=0 loops=18)

25. 0.013 0.062 ↓ 7.0 7 1

Hash (cost=0.36..0.36 rows=1 width=27) (actual time=0.062..0.062 rows=7 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
26. 0.049 0.049 ↓ 7.0 7 1

Index Scan using "UserNotification_eAccountNo" on "UserNotification" "UserNotification9" (cost=0.14..0.36 rows=1 width=27) (actual time=0.028..0.049 rows=7 loops=1)

  • Index Cond: (("eAccountNo")::integer = 1229984023)