Re: Non-unique index performance

Sezai YILMAZ wrote:
> Richard Huxton wrote:
>>
>> OK - all very simple. And you've said there are about 580,000 rows.
>>
>>> test=> explain analyze select id,name from person where id in
>>> ('17201', '338191', '244319', '515209', '20415');
>>
>> Why are you quoting integers?
>
> I qouted them to use indexes. The other method is type casting the
> values to indexed column type. I prefer the quoting method.

Sorry - this is just plain wrong.

If you had an int8 column and a value such as 17, then PG looked at 17
and said Ha! an int4. Then it would not use your index. In such cases
you could either cast the value to int8, or quote it (so that the
planner decided its type later in the process).

This was never required if you had a value that was large enough to be
int8 but not int4, nor when the column was int4. It is not an issue at
all as of version 8.0.

So - if the column is a plain old int4 - just do things normally.

>> Hmm - for some reason it's expecting 14427 rows to be returned. If
>> there were that many matches, then it might well be a better choice
>> than going back and fore between the index and the table all the time.
>>
>> So - we need to find out why it thinks there will be so many rows
>> returned.
>>
>> 1. VACUUM FULL ANALYSE person;
>> 2. re-run the explain
>
>
> This solved the problem. Now it takes about 213 ms.

If you'd analysed frequently anyway, perhaps repeat the steps a few
times and make sure the problem doesn't re-occur. If it does re-occur,
you'll want to increase the column statistics like I'd described.

--
Richard Huxton
Archonet Ltd