explain.depesz.com

PostgreSQL's explain analyze made readable

Result: UHq6

Settings
# exclusive inclusive rows x rows loops node
1. 0.001 430,734.396 ↓ 0.0 0 1

Result (cost=991.04..1,008.00 rows=13 width=918) (actual time=430,734.396..430,734.396 rows=0 loops=1)

2. 0.033 430,734.395 ↓ 0.0 0 1

Sort (cost=991.04..991.07 rows=13 width=1,495) (actual time=430,734.395..430,734.395 rows=0 loops=1)

  • Sort Key: ((('now'::cstring)::date - ("CaseHist"."eventDate")::date)) DESC
  • Sort Method: quicksort Memory: 25kB
3. 0.001 430,734.362 ↓ 0.0 0 1

Nested Loop (cost=48.54..990.80 rows=13 width=1,495) (actual time=430,734.362..430,734.362 rows=0 loops=1)

4. 0.060 430,734.361 ↓ 0.0 0 1

Nested Loop Anti Join (cost=48.11..984.42 rows=13 width=939) (actual time=430,734.361..430,734.361 rows=0 loops=1)

5. 108.167 430,727.281 ↓ 60.0 780 1

Nested Loop Anti Join (cost=47.25..959.10 rows=13 width=939) (actual time=18,393.888..430,727.281 rows=780 loops=1)

6. 2,597.485 12,802.115 ↓ 114,157.6 2,283,153 1

Nested Loop (cost=46.69..911.77 rows=20 width=943) (actual time=1.241..12,802.115 rows=2,283,153 loops=1)

7. 2,801.455 5,638.324 ↓ 114,157.6 2,283,153 1

Nested Loop (cost=46.41..905.82 rows=20 width=916) (actual time=1.209..5,638.324 rows=2,283,153 loops=1)

  • Join Filter: ((("Case"."caseNo")::text = ("CaseHist"."caseNo")::text) AND ((('now'::cstring)::date - ("CaseHist"."eventDate")::date) >= "WCIS"."lastEventAgeLimit"))
  • Rows Removed by Join Filter: 62,889
8. 42.212 1,718.773 ↓ 1,500.8 7,504 1

Hash Join (cost=45.84..884.05 rows=5 width=895) (actual time=1.164..1,718.773 rows=7,504 loops=1)

  • Hash Cond: (("AgeOfCaseDetail"."periodSeqNo")::integer = ("SP"."periodSeqNo")::integer)
9. 127.970 1,676.513 ↓ 3,562.5 523,689 1

Nested Loop (cost=43.92..880.28 rows=147 width=899) (actual time=1.102..1,676.513 rows=523,689 loops=1)

10. 0.053 0.053 ↑ 1.0 1 1

Index Scan using "Ctofc_pkey" on "Ctofc" "Ctofc114" (cost=0.28..0.50 rows=1 width=416) (actual time=0.052..0.053 rows=1 loops=1)

  • Index Cond: (("ctofcNo")::text = '2685'::text)
11. 115.462 1,548.490 ↓ 3,562.5 523,689 1

Nested Loop (cost=43.64..878.31 rows=147 width=483) (actual time=1.049..1,548.490 rows=523,689 loops=1)

  • Join Filter: (("Case"."caseNo")::text = ("DocImageMetaData115"."caseNo")::text)
12. 4.154 236.428 ↓ 117.3 5,983 1

Nested Loop (cost=43.21..793.92 rows=51 width=63) (actual time=0.976..236.428 rows=5,983 loops=1)

  • Join Filter: ("AgeOfCaseDetail"."ageOfCase" >= "WCIS"."caseAgeLimit")
  • Rows Removed by Join Filter: 7,521
13. 2.375 180.706 ↓ 12.9 4,688 1

Hash Join (cost=42.78..441.18 rows=363 width=46) (actual time=0.836..180.706 rows=4,688 loops=1)

  • Hash Cond: ((("CaseType"."caseType")::text = ("WCIS"."caseType")::text) AND (("Case"."wcisClsCode")::text = ("WCIS"."wcisClsCode")::text))
14. 1.800 177.642 ↓ 1.2 5,022 1

Hash Join (cost=1.58..335.72 rows=4,214 width=50) (actual time=0.134..177.642 rows=5,022 loops=1)

  • Hash Cond: (("Case"."caseType")::text = ("CaseType"."caseType")::text)
15. 175.815 175.815 ↓ 1.2 5,022 1

Index Scan using "Case_respCtofcNo_countyNo" on "Case" (cost=0.43..282.16 rows=4,214 width=29) (actual time=0.093..175.815 rows=5,022 loops=1)

  • Index Cond: ((("countyNo")::smallint = 13) AND (("respCtofcNo")::text = '2685'::text))
16. 0.009 0.027 ↑ 1.0 47 1

Hash (cost=0.57..0.57 rows=47 width=21) (actual time=0.027..0.027 rows=47 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 11kB
17. 0.018 0.018 ↑ 1.0 47 1

Seq Scan on "CaseType" (cost=0.00..0.57 rows=47 width=21) (actual time=0.013..0.018 rows=47 loops=1)

18. 0.288 0.689 ↑ 1.0 1,548 1

Hash (cost=17.98..17.98 rows=1,548 width=17) (actual time=0.689..0.689 rows=1,548 loops=1)

  • Buckets: 2,048 Batches: 1 Memory Usage: 94kB
19. 0.401 0.401 ↑ 1.0 1,548 1

Seq Scan on "WcisClsCode" "WCIS" (cost=0.00..17.98 rows=1,548 width=17) (actual time=0.008..0.401 rows=1,548 loops=1)

20. 51.568 51.568 ↑ 2.3 3 4,688

Index Scan using "AgeOfCaseDetail_clockNotStopped" on "AgeOfCaseDetail" (cost=0.42..0.88 rows=7 width=23) (actual time=0.006..0.011 rows=3 loops=4,688)

  • Index Cond: ((("caseNo")::text = ("Case"."caseNo")::text) AND (("countyNo")::smallint = 13))
21. 1,196.600 1,196.600 ↓ 2.4 88 5,983

Index Scan using "DocImageMetaData_filed" on "DocImageMetaData" "DocImageMetaData115" (cost=0.43..1.19 rows=37 width=420) (actual time=0.018..0.200 rows=88 loops=5,983)

  • Index Cond: ((("caseNo")::text = ("AgeOfCaseDetail"."caseNo")::text) AND (("countyNo")::smallint = 13))
22. 0.005 0.048 ↑ 5.0 1 1

Hash (cost=1.86..1.86 rows=5 width=4) (actual time=0.048..0.048 rows=1 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
23. 0.043 0.043 ↑ 5.0 1 1

Index Scan using unique_periods on "StatPeriod" "SP" (cost=0.16..1.86 rows=5 width=4) (actual time=0.042..0.043 rows=1 loops=1)

  • Index Cond: (((('now'::cstring)::date - 1) >= ("beginDate")::date) AND ((('now'::cstring)::date - 1) <= ("endDate")::date))
24. 1,118.096 1,118.096 ↓ 3.5 313 7,504

Index Scan using "CaseHist_pkey" on "CaseHist" (cost=0.56..2.35 rows=89 width=25) (actual time=0.005..0.149 rows=313 loops=7,504)

  • Index Cond: ((("caseNo")::text = ("DocImageMetaData115"."caseNo")::text) AND (("countyNo")::smallint = 13))
25. 4,566.306 4,566.306 ↑ 1.0 1 2,283,153

Index Scan using "HistEvent_pkey" on "HistEvent" (cost=0.28..0.30 rows=1 width=35) (actual time=0.002..0.002 rows=1 loops=2,283,153)

  • Index Cond: (("eventType")::text = ("CaseHist"."eventType")::text)
26. 417,816.999 417,816.999 ↑ 30.0 1 2,283,153

Index Scan using "CaseHist_pkey" on "CaseHist" "CH2" (cost=0.56..3.01 rows=30 width=21) (actual time=0.183..0.183 rows=1 loops=2,283,153)

  • Index Cond: ((("caseNo")::text = ("CaseHist"."caseNo")::text) AND (("countyNo")::smallint = ("CaseHist"."countyNo")::smallint) AND (("countyNo")::smallint = 13))
  • Filter: (ROW(("eventDate")::date, ("histSeqNo")::smallint) > ROW(("CaseHist"."eventDate")::date, ("CaseHist"."histSeqNo")::smallint))
  • Rows Removed by Filter: 376
27. 1.560 7.020 ↑ 1.0 1 780

Nested Loop (cost=0.86..1.94 rows=1 width=15) (actual time=0.009..0.009 rows=1 loops=780)

28. 3.120 3.120 ↑ 1.0 1 780

Index Scan using "Cal_caseNoOnly" on "Cal" "Cal2" (cost=0.43..1.43 rows=1 width=19) (actual time=0.004..0.004 rows=1 loops=780)

  • Index Cond: (("caseNo")::text = ("Case"."caseNo")::text)
  • Filter: (("calDispoCode" IS NULL) AND (("countyNo")::smallint = 13) AND (("countyNo")::smallint = ("Case"."countyNo")::smallint))
  • Rows Removed by Filter: 0
29. 2.340 2.340 ↑ 1.0 1 780

Index Only Scan using "CalDate_range" on "CalDate" cd2 (cost=0.43..0.50 rows=1 width=6) (actual time=0.003..0.003 rows=1 loops=780)

  • Index Cond: (("calSeqNo" = ("Cal2"."calSeqNo")::integer) AND ("countyNo" = 13) AND (start >= ('now'::cstring)::date))
  • Heap Fetches: 163
30. 0.000 0.000 ↓ 0.0 0

Index Scan using "Case_pkey" on "Case" "Case113" (cost=0.43..0.45 rows=1 width=468) (never executed)

  • Index Cond: ((("caseNo")::text = ("CaseHist"."caseNo")::text) AND (("countyNo")::smallint = 13))
Planning time : 75.944 ms
Execution time : 430,736.010 ms