Greg Hennessy <greg.hennessy@gmail.com> writes:
> I'm trying to include a sensitivity operator in a function. My issue is
> that when I have my function, I get a call to SupportRequestSimplify, but
> not SupportRequestSensitivity. It is not obvious what I am doing that is
> incorrect.
Attaching a support function to a SQL-language function seems pretty
weird to me. I think probably what is happening is that the SQL
function is getting inlined and thus there is nothing left to apply
the selectivity hook to. simplify_function() will try the
SupportRequestSimplify hook before it tries inlining, so the fact
that that one registers isn't at odds with this theory.
Is there a way to set the selectivity of a SQL-language function? My use
case is I'm an astronomer, matching large star catalogs, and if I have
a 1e6 star catalog joined with a 1e6 star catalog, the planner estimates
about 1e12 rows, even though the selectivity is about 1e-9 or so.
I don't see a way to define a selectivity function. One of the indexed functions
does have a RESTRICT line with some about of selectivity in the function, but
it isn't apparent it is being referenced.
My issue is that when I have small and medium sized star catalogs, the join
I'm using uses the index, but at a certain large size it stops using the index
and starts using sequential scans, due to the cost of the sequential scan
being smaller than the cost of using the index. I surmise that the cost of
reading in the index, and the use of random_page_cost = 1.2 makes the
sequential scan seem cheaper/faster, even though as a human I know
that using the index scan would be faster. I'm just not sure how to convince
postgresql to calculate the costs properly.