From: | "Jozsef Szalay" <jszalay(at)storediq(dot)com> |
---|---|
To: | <pgsql-performance(at)postgresql(dot)org> |
Subject: | Re: Like 'name%' is not using index |
Date: | 2006-03-03 02:48:51 |
Message-ID: | E387E2E9622FDD408359F98BF183879E312F0C@dc1.storediq.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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(at)paradise(dot)net(dot)nz]
Sent: Thursday, March 02, 2006 7:29 PM
To: Jozsef Szalay
Cc: pgsql-performance(at)postgresql(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Greg Stark | 2006-03-03 04:01:39 | Re: Like 'name%' is not using index |
Previous Message | Mark Kirkwood | 2006-03-03 01:28:40 | Re: Like 'name%' is not using index |