Searching for: JULIA NIGEL (Jan. 1, 1990)
Explaining: trigram (KNN) query
pg_trgm KNN ranking, cut at similarity() ≥ 0.3 per name.
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", (similarity(first_name, JULIA)) AS "_first_sim", (similarity(last_name, NIGEL)) AS "_last_sim", ("records_courtrecord"."last_name" <-> NIGEL) AS "_last_dist", ("records_courtrecord"."first_name" <-> JULIA) AS "_first_dist" FROM "records_courtrecord" WHERE ("records_courtrecord"."date_of_birth" = 1990-01-01 AND (similarity(first_name, JULIA)) >= 0.3 AND (similarity(last_name, NIGEL)) >= 0.3) ORDER BY 10 ASC, 11 ASC LIMIT 200
Query plan
EXPLAIN (ANALYZE, BUFFERS)
Limit (cost=2532.89..2533.39 rows=200 width=80) (actual time=7.659..7.664 rows=7.00 loops=1)
Buffers: shared hit=1499
-> Sort (cost=2532.89..2533.51 rows=248 width=80) (actual time=7.658..7.660 rows=7.00 loops=1)
Sort Key: (((last_name)::text <-> 'NIGEL'::text)), (((first_name)::text <-> 'JULIA'::text))
Sort Method: quicksort Memory: 25kB
Buffers: shared hit=1499
-> Index Scan using idx_person_date_of_birth on records_courtrecord (cost=0.56..2523.02 rows=248 width=80) (actual time=4.777..7.590 rows=7.00 loops=1)
Index Cond: (date_of_birth = '1990-01-01'::date)
Filter: ((similarity((first_name)::text, 'JULIA'::text) >= '0.3'::double precision) AND (similarity((last_name)::text, 'NIGEL'::text) >= '0.3'::double precision))
Rows Removed by Filter: 2189
Index Searches: 1
Buffers: shared hit=1496
Planning:
Buffers: shared hit=328
Planning Time: 2.457 ms
Execution Time: 7.774 ms