On Thu, May 26, 2022 at 3:10 PM Tom Lane <t...@sss.pgh.pa.us> wrote: > Greg Hennessy <greg.henne...@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. >
On Thu, May 26, 2022 at 3:10 PM Tom Lane <t...@sss.pgh.pa.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.
