Thanks for the answer.
The order is relevant because I have queries that use only the first column, and would suffer from the same problem if I switch the order...
In order to plan the best workarounds, this has a chance to be fixed in a near future?
Regards, Alex.
PG Bug reporting form <noreply@postgresql.org> writes:
> As seen in the explain results, when I search for non-existent values of n2,
> the plan changes on Index condition used, giving poor plans. None of this
> plan differences appear if the index is btree. It seems something gist
> related, and I need to use gist due to PostGIS functions.
I don't have time to dig in the code right now, but my recollection is
that btcostestimate() has fairly detailed modeling of the behavior of
queries that constrain only some columns of an index, eg it understands
that "col1 = constant" is much cheaper to scan than "col2 = constant".
On the other hand, gistcostestimate() has no such modeling and assumes
that a constraint on a lower-order column is worth the same as one on
the first column.
This is partially due to lack of effort put into that function, but I seem
to recall being told that GIST was not as sensitive to column ordering
as btree, too. Your results indicate otherwise :-(
Depending on what other queries you use, maybe an adequate workaround
would be to switch the two columns of the index.
regards, tom lane
--
Alex
acamargo@gmail.com"Por que, no mundo, os maus, tão frequentemente, sobrepujam os bons em influência?
-Pela fraqueza dos bons; Os maus são intrigantes e audaciosos, os bons são tímidos. Quando estes o quiserem, dominarão." -- Livro dos Espíritos, Q932.
http://livrodosespiritos.wordpress.com/