#	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: 1,201,872
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: 262,144 Batches: 4 Memory Usage: 14,103kB
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: 1,024 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: 1,215,206
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: 124,792
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: 262,144 Batches: 4 Memory Usage: 14,103kB
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

                                                                                                                                                                                             QUERY PLAN                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=180085.12..180087.46 rows=234 width=475) (actual time=8018.827..8018.912 rows=156 loops=1)
   ->  HashAggregate  (cost=180085.12..180087.46 rows=234 width=475) (actual time=8018.826..8018.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"
         ->  Append  (cost=17761.14..180069.32 rows=234 width=475) (actual time=521.435..8018.085 rows=156 loops=1)
               ->  Hash Anti Join  (cost=17761.14..77276.48 rows=105 width=230) (actual time=521.434..4152.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))
                     ->  Nested Loop Left Join  (cost=17760.26..77219.12 rows=105 width=386) (actual time=521.030..4147.600 rows=149 loops=1)
                           Join Filter: false
                           ->  Nested Loop  (cost=17760.26..77218.07 rows=105 width=304) (actual time=521.028..4147.366 rows=149 loops=1)
                                 ->  Hash Right Join  (cost=17759.83..77169.01 rows=105 width=294) (actual time=520.981..4142.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
                                       ->  Seq Scan on "JdgmtParty" "JdgmtParty30"  (cost=0.00..14550.38 rows=1201928 width=237) (actual time=0.026..743.153 rows=1201953 loops=1)
                                       ->  Hash  (cost=7329.21..7329.21 rows=561561 width=59) (actual time=485.409..485.409 rows=561573 loops=1)
                                             Buckets: 262144  Batches: 4  Memory Usage: 14103kB
                                             ->  Seq Scan on "CivilJdgmt" "CivilJdgmt28"  (cost=0.00..7329.21 rows=561561 width=59) (actual time=0.031..259.994 rows=561573 loops=1)
                                 ->  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))
                           ->  Result  (cost=0.00..0.00 rows=0 width=82) (actual time=0.000..0.000 rows=0 loops=149)
                                 One-Time Filter: false
                     ->  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
                           ->  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)
               ->  Nested Loop  (cost=17784.92..102790.50 rows=129 width=230) (actual time=1733.351..3865.522 rows=10 loops=1)
                     ->  Hash Right Join  (cost=17784.49..102663.15 rows=129 width=359) (actual time=1733.272..3864.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
                           ->  Merge Left Join  (cost=24.66..37227.52 rows=1201928 width=330) (actual time=0.127..2072.848 rows=1215148 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
                                 ->  Index Scan using "JdgmtParty_pkey" on "JdgmtParty" "JdgmtParty68"  (cost=0.43..21292.55 rows=1201928 width=237) (actual time=0.058..1431.286 rows=1201953 loops=1)
                                 ->  Materialize  (cost=0.42..3440.28 rows=173655 width=112) (actual time=0.023..339.752 rows=302303 loops=1)
                                       ->  Index Scan using "Alias_pkey" on "Alias" "Alias69"  (cost=0.42..3006.14 rows=173655 width=112) (actual time=0.018..286.944 rows=173635 loops=1)
                           ->  Hash  (cost=7329.21..7329.21 rows=561561 width=59) (actual time=342.581..342.581 rows=561573 loops=1)
                                 Buckets: 262144  Batches: 4  Memory Usage: 14103kB
                                 ->  Seq Scan on "CivilJdgmt" "CivilJdgmt66"  (cost=0.00..7329.21 rows=561561 width=59) (actual time=0.016..165.536 rows=561573 loops=1)
                     ->  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: 8019.308 ms
(45 rows)

Per node type stats

node type	count	sum of times	% of query
Append	1	0.075 ms	0.0 %
Hash	3	402.470 ms	5.0 %
Hash Anti Join	1	4.765 ms	0.1 %
Hash Right Join	2	4,363.632 ms	54.4 %
HashAggregate	1	0.816 ms	0.0 %
Index Scan	5	1,722.705 ms	21.5 %
Limit	1	0.011 ms	0.0 %
Materialize	1	52.808 ms	0.7 %
Merge Left Join	1	301.810 ms	3.8 %
Nested Loop	2	0.903 ms	0.0 %
Nested Loop Left Join	1	0.234 ms	0.0 %
Result	1	0.000 ms	0.0 %
Seq Scan	3	1,168.683 ms	14.6 %

Per table stats

Table name	Scan count	Total time	% of query
scan type	count	sum of times	% of table
"Alias"	2	287.057 ms	3.6 %
Index Scan	2	287.057 ms	100.0 %
"Case"	2	4.362 ms	0.1 %
Index Scan	2	4.362 ms	100.0 %
"CivilJdgmt"	2	425.530 ms	5.3 %
Seq Scan	2	425.530 ms	100.0 %
"JdgmtParty"	2	2,174.439 ms	27.1 %
Index Scan	1	1,431.286 ms	65.8 %
Seq Scan	1	743.153 ms	34.2 %

explain.depesz.com

PostgreSQL's explain analyze made readable

Result: p4fF

Color mode:

Visible columns:

Per node type stats

Per table stats