pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  

help / color / mirror / Atom feed

Like 'name%' is not using index
4+ messages / 3 participants
[nested] [flat]

* Like 'name%' is not using index
@ 2006-03-03 00:15  Jozsef Szalay <jszalay@storediq.com>
  0 siblings, 2 replies; 4+ messages in thread

From: Jozsef Szalay @ 2006-03-03 00:15 UTC (permalink / raw)
  To: pgsql-performance

Hi all,

 

I have to provide a pretty standard query that should return every row
where the NAME attribute begins with a specific string. The type of the
NAME column is varchar. I do have an index for this column. One would
think that Postgres will use the index to look up the matches, but
apparently that is not the case. It performs a full table scan.  My
query looks something like this:

 

SELECT * FROM table WHERE name LIKE 'smith%';

 

Does anyone know a way to "force" the optimizer to utilize the index? Is
there perhaps another way of doing this?

 

Thanks for the help!

Jozsef

^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: Like 'name%' is not using index
@ 2006-03-03 01:28  Mark Kirkwood <markir@paradise.net.nz>
  parent: Jozsef Szalay <jszalay@storediq.com>
  1 sibling, 0 replies; 4+ messages in thread

From: Mark Kirkwood @ 2006-03-03 01:28 UTC (permalink / raw)
  To: Jozsef Szalay <jszalay@storediq.com>; +Cc: pgsql-performance

Jozsef Szalay wrote:
> Hi all,
> 
>  
> 
> I have to provide a pretty standard query that should return every row 
> where the NAME attribute begins with a specific string. The type of the 
> NAME column is varchar. I do have an index for this column. One would 
> think that Postgres will use the index to look up the matches, but 
> apparently that is not the case. It performs a full table scan.  My 
> query looks something like this:
> 
>  
> 
> SELECT * FROM table WHERE name LIKE ‘smith%’;
> 
>  
> 
> Does anyone know a way to “force” the optimizer to utilize the index? Is 
> there perhaps another way of doing this?
> 

Can you provide an EXPLAIN ANALYZE for the query? This will give us a 
hint as to why the index has not been chosen.

The other standard gotcha is that LIKE will not use an index if your 
cluster is initialized with locale != C. If it is, then you can try 
recreating the index using something like:

CREATE INDEX table_name ON table (name varchar_pattern_ops);

cheers

Mark



^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: Like 'name%' is not using index
@ 2006-03-03 02:48  Jozsef Szalay <jszalay@storediq.com>
  0 siblings, 0 replies; 4+ messages in thread

From: Jozsef Szalay @ 2006-03-03 02:48 UTC (permalink / raw)
  To: pgsql-performance

The var_char_pattern_ops operator group has made the difference. 

Thanks for the help!
Jozsef

-----Original Message-----
From: Mark Kirkwood [mailto:markir@paradise.net.nz] 
Sent: Thursday, March 02, 2006 7:29 PM
To: Jozsef Szalay
Cc: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] Like 'name%' is not using index

Jozsef Szalay wrote:
> Hi all,
> 
>  
> 
> I have to provide a pretty standard query that should return every row

> where the NAME attribute begins with a specific string. The type of
the 
> NAME column is varchar. I do have an index for this column. One would 
> think that Postgres will use the index to look up the matches, but 
> apparently that is not the case. It performs a full table scan.  My 
> query looks something like this:
> 
>  
> 
> SELECT * FROM table WHERE name LIKE 'smith%';
> 
>  
> 
> Does anyone know a way to "force" the optimizer to utilize the index?
Is 
> there perhaps another way of doing this?
> 

Can you provide an EXPLAIN ANALYZE for the query? This will give us a 
hint as to why the index has not been chosen.

The other standard gotcha is that LIKE will not use an index if your 
cluster is initialized with locale != C. If it is, then you can try 
recreating the index using something like:

CREATE INDEX table_name ON table (name varchar_pattern_ops);

cheers

Mark




^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: Like 'name%' is not using index
@ 2006-03-03 04:01  Greg Stark <gsstark@mit.edu>
  parent: Jozsef Szalay <jszalay@storediq.com>
  1 sibling, 0 replies; 4+ messages in thread

From: Greg Stark @ 2006-03-03 04:01 UTC (permalink / raw)
  To: Jozsef Szalay <jszalay@storediq.com>; +Cc: pgsql-performance


"Jozsef Szalay" <jszalay@storediq.com> writes:

> One would
> think that Postgres will use the index to look up the matches, but
> apparently that is not the case. It performs a full table scan.  My
> query looks something like this:
> 
> SELECT * FROM table WHERE name LIKE 'smith%';

There are two possible answers here:

First, what does this output on your database?

db=> show lc_collate;

If it's not "C" then the index can't be used. You would have to make a second
special-purpose index specifically for use with LIKE.

Secondly, please send "explain analyze" output for your query. It will show if
the optimizer is simply estimating that the index won't help enough to be
faster than the full table scan.

-- 
greg




^ permalink  raw  reply  [nested|flat] 4+ messages in thread

end of thread, other threads:[~2006-03-03 04:01 UTC | newest]

Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-03-03 00:15 Like 'name%' is not using index Jozsef Szalay <jszalay@storediq.com>
2006-03-03 01:28 ` Mark Kirkwood <markir@paradise.net.nz>
2006-03-03 04:01 ` Greg Stark <gsstark@mit.edu>
2006-03-03 02:48 Re: Like 'name%' is not using index Jozsef Szalay <jszalay@storediq.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox