I greatly appreciate all the replies. Thanks. I also fully understand and
appreciate all the points made - especially that this idea may not have
general value or acceptance as worthwhile. No argument from me. Let me
explain why I am looking to do this to see if that changes any opinions. I
have written a product called QIKR for MySQL that leverages the MySQL query
rewrite feature and places a knowledge expert of SQL rewrite rules as a
preprocessor to the MySQL optimizer. I have defined an extensive set of
rules based on my 30 years of doing code reviews for app developers who
write terrible SQL. Right now QIKR does 100% syntactic analysis (hoping to
do semantic analysis in a later version). For MySQL (which has a less
mature and less robust optimizer) the performance gains are huge - in
excess of 10X. So far QIKR shows about a 2.5X improvement over the
PostgreSQL optimizer when fed bad SQL. I am not saying the
PotsgrSQL optimizer does a poor job, but rather that QIKR was designed for
"garbage in, not garbage out" - so QIKR fixes all the stupid mistakes that
people make which can confuse or even cripple an optimizer. Hence why I am
looking for this hook - and have come to the experts for help. I have two
very large PostgreSQL partner organizations who have asked me to make
QIKR work for PostgreSQL as it does for MySQL. Again, I am willing to pay
for this hook since it's a special request for a special purpose and not
generally worthwhile in many people's opinions - which I cannot argue with.
On Tue, May 26, 2020 at 2:17 AM Konstantin Knizhnik <
k.knizh...@postgrespro.ru> wrote:
>
>
> 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.
>
>
>
>
