pg.ddx.io  pgsql-hackers@postgresql.org mailing list archive  

help / color / mirror / Atom feed
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
Date: Wed, 27 Feb 2019 21:26:15 -0500
Message-ID: <24277.1551320775@sss.pgh.pa.us> (raw)
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>

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 say “yep, sure, you can construct an index condition by putting the && operator between left_arg and right_arg”.

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 got 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 the 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




view thread (38+ messages)  latest in thread

Message-ID: <24277.1551320775@sss.pgh.pa.us>
Permalink:  ../24277.1551320775@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/24277.1551320775@sss.pgh.pa.us

 · 

reply
Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-hackers@postgresql.org
  Cc: tgl@sss.pgh.pa.us, pramsey@cleverelephant.ca, pgsql-hackers@lists.postgresql.org
  Subject: Re: Allowing extensions to supply operator-/function-specific info
  In-Reply-To: <24277.1551320775@sss.pgh.pa.us>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox