Reputation: 255095
Here is the complete table definition:
CREATE TABLE search.tablename
(
id integer NOT NULL,
name character varying(300) NOT NULL,
CONSTRAINT tablename_pkey PRIMARY KEY (id)
)
WITH (
OIDS=FALSE
);
CREATE INDEX tablename_name_idx
ON search.tablename
USING btree
(name COLLATE pg_catalog."default");
It has ~73k rows and was just vacuumed/analyzed by manual run.
What I cannot understand is why this trivial query
SELECT *
FROM "tablename" AS "arn"
WHERE arn.name LIKE 'foo%'
does not use the corresponding index.
The execution plan for this query is
Seq Scan on tablename arn (cost=0.00..1626.96 rows=7 width=47) (actual time=8.682..8.682 rows=0 loops=1)
Filter: ((name)::text ~~ 'foo%'::text)
Rows Removed by Filter: 73197
Total runtime: 8.703 ms
Could anyone point to what I am missing?
UPD: changing column name type to text
changes nothing.
UPD 2: name = 'foo'
predicate expectedly uses index
Upvotes: 2
Views: 1818
Reputation: 32402
Try adding varchar_pattern_ops to your index
CREATE INDEX tablename_name_idx
ON search.tablename
USING btree
(name COLLATE pg_catalog."default" varchar_pattern_ops);
The operator classes text_pattern_ops, varchar_pattern_ops, bpchar_pattern_ops, and name_pattern_ops support B-tree indexes on the types text, varchar, char, and name, respectively. The difference from the default operator classes is that the values are compared strictly character by character rather than according to the locale-specific collation rules. This makes these operator classes suitable for use by queries involving pattern matching expressions (LIKE or POSIX regular expressions) when the server does not use the standard "C" locale.
Upvotes: 3