On 26.05.2020 04:47, Tomas Vondra wrote:
On Mon, May 25, 2020 at 09:21:26PM -0400, Bruce Momjian wrote:
On Mon, May 25, 2020 at 07:53:40PM -0500, Bert Scalzo wrote:
I am reposting this from a few months back (see below). I am not
trying to be a
pest, just very motivated. I really think this feature has merit,
and if not
generally worthwhile, I'd be willing to pay someone to code it for
me as I
don't have strong enough C skills to modify the PostgreSQL code
myself. So
anyone who might have such skills that would be interested, please
contact me:
bertscal...@gmail.com.
I think your best bet is to try getting someone to write a hook
that will do the replacement so that you don't need to modify too much
of the Postgres core code. You will need to have the hook updated for
new versions of Postgres, which adds to the complexity.
I don't think we have a hook to tweak the incoming SQL, though. We only
have post_parse_analyze_hook, i.e. post-parse, at which point we can't
just rewrite the SQL directly. So I guess we'd need new hook.
VOPS extension performs query substitution (replace query to the
original table with query to projection) using post_parse_analysis_hook
and SPI. So I do not understand why some extra hook is needed.
I do however wonder if an earlier hook is a good idea at all - matching
the SQL directly seems like a rather naive approach that'll break easily
due to formatting, upper/lower-case, subqueries, and many other things.
From this standpoint it seems actually better to inspect and tweak the
parse-analyze result. Not sure how to define the rules easily, though.
In some cases we need to know exact parameter value (as in case
SUBSTRING(column,1,3) = 'ABC').
Sometime concrete value of parameter is not important...
Also it is not clear where such pattern-matching transformation should
be used only for the whole query or for any its subtrees?
As for the complexity, I think hooks are fairly low-maintenance in
practice, we tend not to modify them very often, and when we do it's
usually just adding an argument etc.
I am not sure if the proposed approach can really be useful in many cases.
Bad queries are used to be generated by various ORM tools.
But them rarely generate exactly the same query. So defining matching
rules for the whole query tree will rarely work.
It seems to be more useful to have extensible SQL optimizer, which
allows to add user defined rules (may as transformation patterns).
This is how it is done in GCC code optimizer.
Definitely writing such rules is very non-trivial task.
Very few developers will be able to add their own meaningful rules.
But in any case it significantly simplify improvement of optimizer,
although most of problems with choosing optimal plan are
caused by wrong statistic and rue-based optimization can not help here.
