Hi! I'm having some dificulties using indexes;
1. I run the same query (select blah ... order by foo limit bar) in 2
"virtualy" identical machines, both having postgres v7.3.2. The database is
the same (the amount of data is a bit diferent) and machine A has (much)
more shared buffers than Machine B; postgres uses indexes in B but not in
A. If I change the limit from 200 to 100, machine A starts using indexes.
In machine B, the optimizer only stops using indexes in limit 800. Why does
this happen? Is there any memory parameter that controles this behaviour?
2. Is there any way to force the use of indexes?
3. I have a composite index in columns foo and bar and an index in foo. I
noticed that making a query such as select * from table where foo=1 and
bar=2, postgres correctly uses foo_bar_idx. But if I use select * from
table where foo=1 and bar IN (1,2), posgtres uses foo_idx, having much more
inneficiency. I can make select * from table where foo=1 and bar=2 UNION
select * from table where foo=1 and bar=1, but it's quite ugly. Is this
supposed to work like this?
Thanks in advance
--
Pedro Miguel G. Alves pmalves@think.pt
THINK - Tecnologias de Informação www.think.pt
Tel: +351 21 412 56 56 Av. José Gomes Ferreira
Fax: +351 21 412 56 57 nº 13 1495-139 ALGÉS