Thread: specify whitch index to use
hello, 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 ? thanks, razvan radu
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 -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) "If I was god, I would recompile penguin with --enable-fly." (unknow) Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°
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 >