From: Tom Lane <tgl@sss.pgh.pa.us>
To: Paul Ramsey <pramsey@cleverelephant.ca>
cc: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Subject: Re: Allowing extensions to supply operator-/function-specific info
In-reply-to: <BBB5F584-5036-4A11-B5FF-D30632EC9E1A@cleverelephant.ca>
References: <15193.1548028093@sss.pgh.pa.us> <6953.1548297558@sss.pgh.pa.us>
 <6044.1548524131@sss.pgh.pa.us>
 <CA+TgmoYkLjVcbZZ8PYyaCTMYZJCQ8eiod8MSRMDaczjBw=a0bQ@mail.gmail.com>
 <19993.1548697885@sss.pgh.pa.us>
 <CACowWR3jZ4Xo1atU5xTDpEoeeKSw1==sKPhCy3a29HtcE9oQVQ@mail.gmail.com>
 <7671.1551135674@sss.pgh.pa.us>
 <CACowWR3_RyPSYO6a8TVLZ7KnL_rDNh2ywkSTeqFmrAPpP4y6-g@mail.gmail.com>
 <11051.1551139751@sss.pgh.pa.us>
 <CACowWR1ycbLx0t7WwT8uuJnK7E3U+tfzKXBRPaQrgHiCG+hvbA@mail.gmail.com>
 <14747.1551219541@sss.pgh.pa.us>
 <91BAE1F4-7C16-4F87-849F-B01175A8D7D8@cleverelephant.ca>
 <17244.1551226168@sss.pgh.pa.us>
 <74C11775-67A7-48D2-B1E3-24C52A686F86@cleverelephant.ca>
 <15086.1551310830@sss.pgh.pa.us>
 <BBB5F584-5036-4A11-B5FF-D30632EC9E1A@cleverelephant.ca>
Comments: In-reply-to Paul Ramsey <pramsey@cleverelephant.ca>
	message dated "Wed, 27 Feb 2019 15:50:50 -0800"
MIME-Version: 1.0
Content-Type: text/plain; charset="UTF-8"
Content-ID: <24276.1551320775.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Wed, 27 Feb 2019 21:26:15 -0500
Message-ID: <24277.1551320775@sss.pgh.pa.us>
Precedence: bulk

Paul Ramsey <pramsey@cleverelephant.ca> writes:
> I added three indexes to my test table:
>   CREATE INDEX foo_g_gist_x ON foo USING GIST (g);
>   CREATE INDEX foo_g_gist_nd_x ON foo USING GIST (g gist_geometry_ops);
>   CREATE INDEX foo_g_spgist_x ON foo USING SPGIST (g);
> They all support the overlaps (&&) operator.

> So, SupportRequestIndexCondition happens three times, and each time I sa=
y =E2=80=9Cyep, sure, you can construct an index condition by putting the =
&& operator between left_arg and right_arg=E2=80=9D.

Sounds right.

> How does the planner end up deciding on which index to *actually* use?

It's whichever has the cheapest cost estimate.  In case of an exact tie,
I believe it'll choose the index with lowest OID (or maybe highest OID,
not sure).

> The selectivity is the same, the operator is the same. I found that I go=
t the ND GIST one first, then the SPGIST and finally the 2d GIST, which is=
 unfortunate, because the 2D and SPGIST are almost certainly faster than t=
he ND GIST.

Given that it'll be the same selectivity, the cost preference is likely to
go to whichever index is physically smallest, at least for indexes of the
same type.  When they're not the same type there might be an issue with
the index AM cost estimators not being lined up very well as to what they
account for and how.

I don't doubt that there's plenty of work to be done in making the cost
estimates better in cases like this --- in particular, I don't think we
have any way of accounting for the idea that one index opclass might be
smarter than another one for the same query, unless that shakes out as a
smaller index.  But you'd have had the same issues with the old approach.

			regards, tom lane