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@xxxxxxxxxxxxxxxx <pgsql-general@xxxxxxxxxxxxxxxx> 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