Re: selectivity function
| От | Greg Hennessy |
|---|---|
| Тема | Re: selectivity function |
| Дата | |
| Msg-id | CA+mZaOP1VUax7mrRgBQ2mes3GJ9a8W_dF55RhRh+E0dtoT_mFQ@mail.gmail.com обсуждение исходный текст |
| Ответ на | Re: selectivity function (Tom Lane <tgl@sss.pgh.pa.us>) |
| Ответы |
Re: selectivity function
|
| Список | pgsql-hackers |
On Thu, May 26, 2022 at 3:10 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
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.
On Thu, May 26, 2022 at 3:10 PM Tom Lane <tgl@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.
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.
В списке pgsql-hackers по дате отправления: