yes, forgot to mention that the hole database is "VACUUM ANALYZE" after
the index creation, couple of times.
and also my message was incomplete and the problem is in the omission.
the order clause was not mentioned, witch I think is causing the
"problem", by adding both columns in the order clause the index yielding
0 rows is used.
thanks again,
razvan radu
Andreas Kretschmer wrote:
> pgsql-general@list.coretech.ro <pgsql-general@list.coretech.ro> schrieb:
>
>> I have two indexes on a table on cols col1 and col2, the table has ~10M
>> rows on pg v8.1.4
>>
>> when I use "where col1 = val1" the query is fast and returns 0 rows
>> when I use "where col2 > val2" the query is slow and returns ~1M rows
>>
>> in both cases the corresponding indexes are used.
>> when I use "where col1 = val1 and col2 > val2" the query is slow and
>> returns 0 rows using the index on col2
>>
>> is there any way for postgres to use both indexes when relevant columns are
>> present in a where clause ?
>> is it possible to specify which index to use in a query ?
>>
>
> Can you do a 'explain analyse select ...' and tell us the output from
> this? Do you have a recent vacuum?
>
> 8.1 *should* use both indexes in a bitmap index scan.
>
>
> Andreas
>