Propose a new hook for mutating the query bounds

From: Xiaozhe Yao <askxzyao(at)gmail(dot)com>
To: pgsql-hackers(at)lists(dot)postgresql(dot)org
Subject: Propose a new hook for mutating the query bounds
Date: 2021-11-17 13:24:17
Message-ID: CAAxqZp-GDWU6qEs6rFjBQGqxpzobn2cGrSrbULLG8Cc_hdqFLg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Hi hackers,

I am currently working on improving the cardinality estimation component in
PostgreSQL with machine learning. I came up with a solution that mutates
the bounds for different columns. For example, assume that we have a query

```
select * from test where X<10 and Y<20;
```

Our approach tries to learn the relation between X and Y. For example, if
we have a linear relation, Y=X+10. Then Y<20 is essentially equivalent to
X<10. Therefore we can mutate the Y<20 to Y<INT_MAX so that the selectivity
will be 1, and we will have a more accurate estimation.

It seems to me that we can achieve something similar by mutating the
pg_statistics, however, mutating the bounds is something more
straightforward to me and less expensive.

I am wondering if it is possible to have such an extension? Or if there is
a better solution to this? I have already implemented this stuff in a
private repository, and if this is something you like, I can further
propose the patch to the list.

Best regards,
Xiaozhe

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Daniel Gustafsson 2021-11-17 13:25:07 Re: Patch to avoid orphaned dependencies
Previous Message Daniel Gustafsson 2021-11-17 13:16:46 Re: Proposal: allow database-specific role memberships