Re: BUG #15408: Postgresql bad planning with multicolumn gist and search with empty results

=?utf-8?q?PG_Bug_reporting_form?= <noreply(at)postgresql(dot)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