Paul Ramsey <pram...@cleverelephant.ca> writes:
>> On Feb 26, 2019, at 2:19 PM, Tom Lane <t...@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
