Re: New Feature Request

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:
>>> bertscalzo2(at)gmail(dot)com(dot)
>>
>> 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.