Date: Fri, 03 Mar 2006 14:28:40 +1300
From: Mark Kirkwood <markir@paradise.net.nz>
Subject: Re: Like 'name%' is not using index
In-reply-to: <E387E2E9622FDD408359F98BF183879E312F05@dc1.storediq.com>
To: Jozsef Szalay <jszalay@storediq.com>
Cc: pgsql-performance@postgresql.org
Message-id: <44079BC8.3030209@paradise.net.nz>
MIME-version: 1.0
Content-type: text/plain; charset=windows-1252; format=flowed
Content-transfer-encoding: 8BIT
User-Agent: Mozilla Thunderbird 1.0.6 (X11/20051106)
References: <E387E2E9622FDD408359F98BF183879E312F05@dc1.storediq.com>

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