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: Tue, 26 Feb 2019 19:09:28 -0500
Message-ID: <17244.1551226168@sss.pgh.pa.us> (raw)
In-Reply-To: <91BAE1F4-7C16-4F87-849F-B01175A8D7D8@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>
Paul Ramsey <pramsey@cleverelephant.ca> writes:
>> On Feb 26, 2019, at 2:19 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> In most cases, multiple matching arguments are going to lead to
>> failure to construct any useful index condition, because your
>> comparison value has to be a pseudoconstant (ie, not a variable
>> from the same table, so in both of the above examples there's
>> no function argument you could compare to).
> This term “pseudoconstant” has been causing me some worry as it crops up
> in your explanations a fair amount.
It is defined in the documentation, but what it boils down to is that
your comparison value can't contain either (1) variables from the same
table the index is on or (2) volatile functions. There is a function
defined in optimizer.h that can check that for you, so you don't have
to worry too much about the details.
> I expect to have queries of the form
> SELECT a.*, b.*
> FROM a
> JOIN b
> ON ST_Intersects(a.geom, b.geom)
Sure, that's fine. If there are indexes on both a.geom and b.geom,
you'll get separate opportunities to match to each of those, and
what you'd be constructing in each case is an indexqual that has to be
used on the inner side of a nestloop join (so that the outer side can
provide the comparison value). What's not fine is "WHERE
ST_Intersects(a.geom, a.othergeom)" ... you can't make an indexscan
out of that, at least not with the && operator.
regards, tom lane
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: <17244.1551226168@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