explain.depesz.com

PostgreSQL's explain analyze made readable

Result: p4fF

Settings
# exclusive inclusive rows x rows loops node
1. 0.011 8,018.912 ↑ 1.5 156 1

Limit (cost=180,085.12..180,087.46 rows=234 width=475) (actual time=8,018.827..8,018.912 rows=156 loops=1)

2. 0.816 8,018.901 ↑ 1.5 156 1

HashAggregate (cost=180,085.12..180,087.46 rows=234 width=475) (actual time=8,018.826..8,018.901 rows=156 loops=1)

  • Group Key: ("fullNameLastNameFirst"(*)), "JdgmtParty30"."attyNo", "Case29"."caseNo", "Case29"."caseType", "CivilJdgmt28"."civilJdgmtSeqNo", "CivilJdgmt28"."dateDkted", "CivilJdgmt28"."timeDkted", "JdgmtParty30".dob, "CivilJdgmt28"."dorWarrantNo", "CivilJdgmt28"."fullSatisDate", "Case29"."isExpunge", "JdgmtParty30"."isReleased", "CivilJdgmt28"."jdgmtDate", "CivilJdgmt28"."jdgmtLienType", "JdgmtParty30"."jdgmtPartyStatus", "JdgmtParty30"."jdgmtPartyType", "Case29"."countyNo", "Case29"."caseNo", "CivilJdgmt28"."civilJdgmtSeqNo", "JdgmtParty30"."jdgmtPartyNo", "aliasNo", ("fullNameLastNameFirst"("JdgmtParty30".*)), "CivilJdgmt28".satisfaction, ((regexp_replace(upper(("JdgmtParty30"."nameF")::text), '[^A-Z0-9]'::text, ''::text, 'g'::text))::"SearchNameT"), ((regexp_replace(upper(("JdgmtParty30"."nameL")::text), '[^A-Z0-9]'::text, ''::text, 'g'::text))::"SearchNameT"), "CivilJdgmt28"."totAmt", "Case29"."wcisClsCode
3. 0.075 8,018.085 ↑ 1.5 156 1

Append (cost=17,761.14..180,069.32 rows=234 width=475) (actual time=521.435..8,018.085 rows=156 loops=1)

4. 4.765 4,152.488 ↓ 1.4 146 1

Hash Anti Join (cost=17,761.14..77,276.48 rows=105 width=230) (actual time=521.434..4,152.488 rows=146 loops=1)

  • Hash Cond: ((("JdgmtParty30"."countyNo")::smallint = ("Alias37"."countyNo")::smallint) AND (("JdgmtParty30"."caseNo")::text = ("Alias37"."caseNo")::text) AND (("JdgmtParty30"."partyNo")::smallint = ("Alias37"."partyNo")::smallint))
5. 0.234 4,147.600 ↓ 1.4 149 1

Nested Loop Left Join (cost=17,760.26..77,219.12 rows=105 width=386) (actual time=521.030..4,147.600 rows=149 loops=1)

  • Join Filter: false
6. 0.555 4,147.366 ↓ 1.4 149 1

Nested Loop (cost=17,760.26..77,218.07 rows=105 width=304) (actual time=521.028..4,147.366 rows=149 loops=1)

7. 2,914.077 4,142.639 ↓ 1.4 149 1

Hash Right Join (cost=17,759.83..77,169.01 rows=105 width=294) (actual time=520.981..4,142.639 rows=149 loops=1)

  • Hash Cond: ((("JdgmtParty30"."countyNo")::smallint = ("CivilJdgmt28"."countyNo")::smallint) AND (("JdgmtParty30"."caseNo")::text = ("CivilJdgmt28"."caseNo")::text) AND (("JdgmtParty30"."civilJdgmtSeqNo")::smallint = ("CivilJdgmt28"."civilJdgmtSeqNo")::smallint))
  • Filter: (((regexp_replace(upper(("JdgmtParty30"."nameL")::text), '[^A-Z0-9]'::text, ''::text, 'g'::text))::"SearchNameT")::text ~~ 'SMALL%'::text)
  • Rows Removed by Filter: 1201872
8. 743.153 743.153 ↓ 1.0 1,201,953 1

Seq Scan on "JdgmtParty" "JdgmtParty30" (cost=0.00..14,550.38 rows=1,201,928 width=237) (actual time=0.026..743.153 rows=1,201,953 loops=1)

9. 225.415 485.409 ↓ 1.0 561,573 1

Hash (cost=7,329.21..7,329.21 rows=561,561 width=59) (actual time=485.409..485.409 rows=561,573 loops=1)

  • Buckets: 262144 Batches: 4 Memory Usage: 14103kB
10. 259.994 259.994 ↓ 1.0 561,573 1

Seq Scan on "CivilJdgmt" "CivilJdgmt28" (cost=0.00..7,329.21 rows=561,561 width=59) (actual time=0.031..259.994 rows=561,573 loops=1)

11. 4.172 4.172 ↑ 1.0 1 149

Index Scan using "Case_pkey" on "Case" "Case29" (cost=0.43..0.47 rows=1 width=25) (actual time=0.028..0.028 rows=1 loops=149)

  • Index Cond: ((("caseNo")::text = ("CivilJdgmt28"."caseNo")::text) AND (("countyNo")::smallint = ("CivilJdgmt28"."countyNo")::smallint))
12. 0.000 0.000 ↓ 0.0 0 149

Result (cost=0.00..0.00 rows=0 width=82) (actual time=0.000..0.000 rows=0 loops=149)

  • One-Time Filter: false
13. 0.010 0.123 ↓ 1.7 22 1

Hash (cost=0.65..0.65 rows=13 width=17) (actual time=0.123..0.123 rows=22 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
14. 0.113 0.113 ↓ 1.7 22 1

Index Scan using "Alias_searchNameL" on "Alias" "Alias37" (cost=0.42..0.65 rows=13 width=17) (actual time=0.040..0.113 rows=22 loops=1)

  • Index Cond: ((((regexp_replace(upper(("nameL")::text), '[^A-Z0-9]'::text, ''::text, 'g'::text))::"SearchNameT")::text ~>=~ 'SMALL'::text) AND (((regexp_replace(upper(("nameL")::text), '[^A-Z0-9]'::text, ''::text, 'g'::text))::"SearchNameT")::text ~<~ 'SMALM'::text))
  • Filter: (((regexp_replace(upper(("nameL")::text), '[^A-Z0-9]'::text, ''::text, 'g'::text))::"SearchNameT")::text ~~ 'SMALL%'::text)
15. 0.348 3,865.522 ↑ 12.9 10 1

Nested Loop (cost=17,784.92..102,790.50 rows=129 width=230) (actual time=1,733.351..3,865.522 rows=10 loops=1)

16. 1,449.555 3,864.984 ↑ 12.9 10 1

Hash Right Join (cost=17,784.49..102,663.15 rows=129 width=359) (actual time=1,733.272..3,864.984 rows=10 loops=1)

  • Hash Cond: ((("JdgmtParty68"."countyNo")::smallint = ("CivilJdgmt66"."countyNo")::smallint) AND (("JdgmtParty68"."caseNo")::text = ("CivilJdgmt66"."caseNo")::text) AND (("JdgmtParty68"."civilJdgmtSeqNo")::smallint = ("CivilJdgmt66"."civilJdgmtSeqNo")::smallint))
  • Filter: (((regexp_replace(upper(("Alias69"."nameL")::text), '[^A-Z0-9]'::text, ''::text, 'g'::text))::"SearchNameT")::text ~~ 'SMALL%'::text)
  • Rows Removed by Filter: 1215206
17. 301.810 2,072.848 ↓ 1.0 1,215,148 1

Merge Left Join (cost=24.66..37,227.52 rows=1,201,928 width=330) (actual time=0.127..2,072.848 rows=1,215,148 loops=1)

  • Merge Cond: (("JdgmtParty68"."caseNo")::text = ("Alias69"."caseNo")::text)
  • Join Filter: ((("Alias69"."countyNo")::smallint = ("JdgmtParty68"."countyNo")::smallint) AND (("Alias69"."partyNo")::smallint = ("JdgmtParty68"."partyNo")::smallint))
  • Rows Removed by Join Filter: 124792
18. 1,431.286 1,431.286 ↓ 1.0 1,201,953 1

Index Scan using "JdgmtParty_pkey" on "JdgmtParty" "JdgmtParty68" (cost=0.43..21,292.55 rows=1,201,928 width=237) (actual time=0.058..1,431.286 rows=1,201,953 loops=1)

19. 52.808 339.752 ↓ 1.7 302,303 1

Materialize (cost=0.42..3,440.28 rows=173,655 width=112) (actual time=0.023..339.752 rows=302,303 loops=1)

20. 286.944 286.944 ↑ 1.0 173,635 1

Index Scan using "Alias_pkey" on "Alias" "Alias69" (cost=0.42..3,006.14 rows=173,655 width=112) (actual time=0.018..286.944 rows=173,635 loops=1)

21. 177.045 342.581 ↓ 1.0 561,573 1

Hash (cost=7,329.21..7,329.21 rows=561,561 width=59) (actual time=342.581..342.581 rows=561,573 loops=1)

  • Buckets: 262144 Batches: 4 Memory Usage: 14103kB
22. 165.536 165.536 ↓ 1.0 561,573 1

Seq Scan on "CivilJdgmt" "CivilJdgmt66" (cost=0.00..7,329.21 rows=561,561 width=59) (actual time=0.016..165.536 rows=561,573 loops=1)

23. 0.190 0.190 ↑ 1.0 1 10

Index Scan using "Case_pkey" on "Case" "Case67" (cost=0.43..0.47 rows=1 width=25) (actual time=0.019..0.019 rows=1 loops=10)

  • Index Cond: ((("caseNo")::text = ("CivilJdgmt66"."caseNo")::text) AND (("countyNo")::smallint = ("CivilJdgmt66"."countyNo")::smallint))
Planning time : 3.536 ms
Execution time : 8,019.308 ms