Re: selectivity function

On Thu, May 26, 2022 at 3:10 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Greg Hennessy <greg(dot)hennessy(at)gmail(dot)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.
>

On Thu, May 26, 2022 at 3:10 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> 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.
Looking at https://www.postgresql.org/docs/current/sql-createfunction.html
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.