Re: index not used in joins

Richard Huxton wrote:
> Sebastian Böck wrote:
>
>> Richard Huxton wrote:
>>
>>> Can you post the output from your "explain analyse" calls too? The
>>> statistics aren't going to be the same on different machines.
>>>
>>
>> Sure, here it is.
>
>
> Thanks. (PS - remember to cc the list too).

[output of EXPLAIN ANALYZE]

> OK - so what you want to know is why index "test_999" is used in the
> second but not the first, even though both return the same rows.
>
> The fact is that the conditional index:
> CREATE INDEX test_999 ON test (datum)
> WHERE version = '999' OR approved IS NOT NULL;
> AFAIK looks at the WHERE clause of your query to determine where it can
> run. Don't forget that the planner needs to pick which index is best
> *before* it starts fetching data.
>
> So - in the first example there might be rows where e.g. t.version=998
> which means test_999 would be a poor choice of index.

But what if the table users contains only 1 row and the column "version"
has a value of "999"?

Are there any other options to speed up this kind of query?

Thanks so far

Sebastian