Re: BUG #15408: Postgresql bad planning with multicolumn gist and search with empty results - Mailing list pgsql-bugs

From Tom Lane
Subject Re: BUG #15408: Postgresql bad planning with multicolumn gist and search with empty results
Date
Msg-id 1818.1538057011@sss.pgh.pa.us
Whole thread Raw
In response to BUG #15408: Postgresql bad planning with multicolumn gist and searchwith empty results  (PG Bug reporting form <noreply@postgresql.org>)
Responses Re: BUG #15408: Postgresql bad planning with multicolumn gist andsearch with empty results
List pgsql-bugs
=?utf-8?q?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


pgsql-bugs by date:

Previous
From: Tom Lane
Date:
Subject: Re: BUG #15407: [minor] build depends on $MAKELEVEL being 0 at top Makefile
Next
From: Alex Pires de Camargo
Date:
Subject: Re: BUG #15408: Postgresql bad planning with multicolumn gist andsearch with empty results