Re: Allowing extensions to supply operator-/function-specific info

> On Feb 27, 2019, at 3:40 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
>> Variable SupportRequestCost is very exciting, but given that variable cost is usually driven by the complexity of arguments, what kind of argument is the SupportRequestCost call fed during the planning stage? Constant arguments are pretty straight forward, but what gets sent in when a column is one (or all) of the arguments?
>
> You'll see whatever is in the post-constant-folding parse tree. If it's a
> Const, you can look at the value. If it's a Var, you could perhaps look
> at the pg_statistic info for that column, though whether that would give
> you much of a leg up for cost estimation is hard to say. For any sort of
> expression, you're probably going to be reduced to using a default
> estimate. The core code generally doesn't try to be intelligent about
> anything beyond the Const and Var cases.

Actually, this is interesting, maybe there’s something to be done looking at the vertex density of the area under consideration… would require gathering extra stats, but could be useful (maybe, at some point feeding costs into plans has to degenerate into wankery…)

Another question:

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”.

How does the planner end up deciding on which index to *actually* use? 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.

In practice, most people will just have one spatial index at a time, but I still wonder?

P