Pasted below, I demonstrate two queries which both produce the same two
records in their result set (the two objects which have my full name in
their name field). Based on my experiences with other DBMS, I would have
thought Postgres could do a simple index scan for both queries and thus
produce the results equally fast in both cases, but not so. In the
second case it fails to use the index, falling back to filtering a slow
sequential scan.
I can't seem to find anything in the manual about this, but I'm sure
I've read several places that Postgres can do what I want here. The only
place I can dig up right now is in contrib/fulltextindex/README.fti,
which mentions: "If a ~ search starts with a ^ (match start of string),
btree indices can be used by PostgreSQL."
Is there something I am doing wrong?
Thanks in advance,
Jon Valvatne
db=# explain analyze SELECT id FROM object WHERE name = 'Jon Valvatne';
QUERY PLAN
-----------------------------------------------------------------------
Index Scan using idx_n on object (cost=0.00..982.09 rows=335 width=4)
(actual time=0.269..0.336 rows=2 loops=1)
Index Cond: ((name)::text = 'Jon Valvatne'::text)
Total runtime: 0.420 ms
(3 rows)
db=# explain analyze SELECT id FROM object WHERE name ~ '^Jon V';
QUERY PLAN
-----------------------------------------------------------------------
Seq Scan on object (cost=0.00..10706.30 rows=1 width=4)
(actual time=9.504..2665.439 rows=2 loops=1)
Filter: ((name)::text ~ '^Jon V'::text)
Total runtime: 2665.527 ms
(3 rows)
