Thread: Using indexes for like foo% type queries when foo isn't constant (not a locale issue)
Using indexes for like foo% type queries when foo isn't constant (not a locale issue)
From
Greg Stark
Date:
In the following query postgres doesn't use the index. In the hard-coded version below it does. I suppose it can't because it's possible the "target" could have wildcards etc in them. Is there any way to indicate the postgres that that won't happen? This is going to be even more of an issue when preparsed queries happen because even in the hard coded example it will be an issue. I know in Oracle if you parse a query with a LIKE :1||'%' type expression it still plans to use the index and that's extremely useful. I don't know what it does if there's a % in the parameter, it either takes the performance hit or it doesn't treat them as special? db=> explain analyze select postalcode, abs(substr(target,6,1)::integer-substr(postalcode,6,1)::integer) as dist from postalcodes,(select 'L6C2M6'::text as target) as t where postalcode like substr(target,1,5)||'%' order by dist asc limit2; QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------- Limit (cost=12182.77..12182.77 rows=2 width=42) (actual time=9226.17..9226.18 rows=2 loops=1) -> Sort (cost=12182.77..12186.16 rows=1359 width=42) (actual time=9226.16..9226.16 rows=2 loops=1) Sort Key: abs(((substr(t.target, 6, 1))::integer - (substr((postalcodes.postalcode)::text, 6, 1))::integer)) -> Nested Loop (cost=0.00..12112.04 rows=1359 width=42) (actual time=3262.89..9205.25 rows=8 loops=1) Join Filter: ("inner".postalcode ~~ (substr("outer".target, 1, 5) || '%'::text)) -> Subquery Scan t (cost=0.00..0.01 rows=1 width=0) (actual time=0.04..0.05 rows=1 loops=1) -> Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.02..0.02 rows=1 loops=1) -> Seq Scan on postalcodes (cost=0.00..7335.69 rows=271769 width=10) (actual time=5.52..3268.74 rows=271769loops=1) Total runtime: 9241.92 msec (9 rows) db=> explain analyze select postalcode, abs(substr('L6C2M6',6,1)::integer-substr(postalcode,6,1)::integer) as dist from postalcodeswhere postalcode like substr('L6C2M6',1,5)||'%' order by dist asc limit 2; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------- Limit (cost=3.29..3.29 rows=1 width=10) (actual time=36.54..36.55 rows=2 loops=1) -> Sort (cost=3.29..3.29 rows=1 width=10) (actual time=36.53..36.54 rows=2 loops=1) Sort Key: abs((6 - (substr((postalcode)::text, 6, 1))::integer)) -> Index Scan using idx_postalcodes_postalcodeon on postalcodes (cost=0.00..3.28 rows=1 width=10) (actual time=35.91..36.33rows=8 loops=1) Index Cond: ((postalcode >= 'L6C2M'::bpchar) AND (postalcode < 'L6C2N'::bpchar)) Filter: (postalcode ~~ 'L6C2M%'::text) Total runtime: 36.93 msec (7 rows) -- greg