Thread: Postgres doesn't use indexes for prefix matching?

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)

Your message from 7/2 just showed up today.

> db=# explain analyze SELECT id FROM object WHERE name ~ '^Jon V';

I use leading substring indexing all the time.  Try:

  SELECT id FROM object WHERE name like 'Jon V%';

Wes

On Fri, 2 Jul 2004, Jon Valvatne wrote:

> 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?

Prefix matching using indexes is available in "C" locale or if you have
an index in a special opclass (<something>_pattern_ops -- I think the
operator class part of the docs describe them).