Searching for: JULIA NIGEL (Jan. 1, 1990)
Explaining: soundex + levenshtein
Phonetic pre-filter using SOUNDEX codes. Edit distance ≤ 2 — precision filter applied on top of other modes.
SQL
SELECT "records_courtrecord"."id", "records_courtrecord"."first_name", "records_courtrecord"."last_name", "records_courtrecord"."middle_name", "records_courtrecord"."date_of_birth", "records_courtrecord"."nicknames", "records_courtrecord"."person_id", levenshtein_less_equal(UPPER("records_courtrecord"."first_name"), JULIA, 2) AS "_fn_dist", levenshtein_less_equal(UPPER("records_courtrecord"."last_name"), NIGEL, 2) AS "_ln_dist" FROM "records_courtrecord" WHERE (((SOUNDEX(first_name) = SOUNDEX(JULIA)) AND (SOUNDEX(last_name) = SOUNDEX(NIGEL))) AND "records_courtrecord"."date_of_birth" = 1990-01-01 AND levenshtein_less_equal(UPPER("records_courtrecord"."first_name"), JULIA, 2) <= 2 AND levenshtein_less_equal(UPPER("records_courtrecord"."last_name"), NIGEL, 2) <= 2) ORDER BY "records_courtrecord"."date_of_birth" DESC LIMIT 200
Query plan
EXPLAIN (ANALYZE, BUFFERS)
Limit (cost=2297.25..2309.77 rows=1 width=72) (actual time=40.970..41.034 rows=7.00 loops=1)
Buffers: shared hit=208
-> Bitmap Heap Scan on records_courtrecord (cost=2297.25..2309.77 rows=1 width=72) (actual time=40.968..41.030 rows=7.00 loops=1)
Recheck Cond: ((date_of_birth = '1990-01-01'::date) AND (soundex((first_name)::text) = 'J400'::text))
Filter: ((soundex((last_name)::text) = 'N240'::text) AND (levenshtein_less_equal(upper((first_name)::text), 'JULIA'::text, 2) <= 2) AND (levenshtein_less_equal(upper((last_name)::text), 'NIGEL'::text, 2) <= 2))
Rows Removed by Filter: 46
Heap Blocks: exact=13
Buffers: shared hit=208
-> BitmapAnd (cost=2297.25..2297.25 rows=11 width=0) (actual time=40.808..40.809 rows=0.00 loops=1)
Buffers: shared hit=195
-> Bitmap Index Scan on idx_person_date_of_birth (cost=0.00..19.54 rows=2236 width=0) (actual time=0.317..0.317 rows=2196.00 loops=1)
Index Cond: (date_of_birth = '1990-01-01'::date)
Index Searches: 1
Buffers: shared hit=6
-> Bitmap Index Scan on idx_person_first_name_soundex (cost=0.00..2277.47 rows=270000 width=0) (actual time=38.744..38.744 rows=220388.00 loops=1)
Index Cond: (soundex((first_name)::text) = 'J400'::text)
Index Searches: 1
Buffers: shared hit=189
Planning:
Buffers: shared hit=278
Planning Time: 2.224 ms
Execution Time: 41.169 ms