search

ossc-db / pg_hint_plan

Extension adding support for optimizer hints in PostgreSQL
Other
710 stars 103 forks source link

Is it possible to inject each operator's cardinality by hint 'Rows' ? #167

Closed Wind-Gone closed 2 months ago

Wind-Gone commented 10 months ago

We aim to modify each operator on the PG query tree to a specified cardinality, but according to the documentation, we only found that cardinalities for two-table joins can be specified. We would like to know how to specify cardinalities for operators like selection and for multiple nested joins. For example, for the following three-table join query, how can I write hints to specify the cardinalities for two joins and two selections?

SELECT * FROM t1, t2, t3 WHERE t1.id1 = t2.id1 AND t2.id2 = t3.id2 AND t1.money < 10 AND t2.name = 'Tom';

Meanwhile, we notice that many research papers have chosen the great 'pg_hint_plan' tool to inject real cardinality. For example, 2022 VLDB Cardinality Estimation in DBMS: A Comprehensive Benchmark Evaluation presented that they use this tool to inject cardinality but they didn't emphasize that it can only work on partial operators. We want to know whether we make mistakes or we can have more clearly explanation to use it. image

Thanks for your help！ Best wishes ！

michaelpq commented 2 months ago

We aim to modify each operator on the PG query tree to a specified cardinality, but according to the documentation, we only found that cardinalities for two-table joins can be specified.

You are asking for a specific feature, hence I'd suggest to send a patch to get support for it rather than waiting for someone to do it :)

I am closing the ticket for now. If you can propose a feature to be able to have more control on the cardinality of the nested joins, why not, but please send a pull request.