pg_hint_plan
pg_hint_plan copied to clipboard
Is it possible to inject each opearator's cardinality by hint 'Rows' ?
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.
Thanks for your help! Best wishes !