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

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.

On Thu, Sep 27, 2018 at 11:03 AM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> =?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
>

--
Alex
acamargo(at)gmail(dot)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/