Re: Access to postgresql query optimizer output

On 29 October 2012 16:00, Jeff Janes <jeff(dot)janes(at)gmail(dot)com> wrote:
> The re-writing does not occur at the level of SQL, but rather at the
> level of an internal representation "parse tree".

Right. The post parse-analysis tree (i.e. the Query struct) is
potentially rewritten by an explicit rewrite stage after
parse-analysis but immediately before planning. No rewriting of plans
actually occurs. This is part of the rules subsystem, which is
generally considered to be a foot-gun. It *is* mostly just as if the
query had been silently rewritten, and had its SQL magically changed.

Craig mentioned that two distinct queries can produce the same query
plan. That's true, if a little academic. I guess the best example of
that is with join syntax. Technically, these could be two distinct
queries, in that the query trees would be substantively different
(according to pg_stat_statements own definition of that, which is
essentially that they're not bitwise identical in respect of their
nodes' substantive fields):

select * from foo f join bar b on f.bid = b.bid;

select * from foo f, bar b where f.bid = b.bid;

However, after planning, they could indeed have identical plans.
Still, those plans don't relate to some alternative query to the one
provided, assuming rule expansion didn't occur, and if rule expansion
did occur, I think you could just pretend that you'd written the SQL
that way in the first place and it would work just the same.

There might be some other instances where two distinct queries have
identical plan tree structures. Consider these two:

select upper(lower('text'));
select upper(upper('text'));

So what actually occurs is that the underlying pg_proc accessible
functions (upper() and lower()) are actually executed in
preprocess_expression() during planning, not execution proper. All
that appears within the plan tree is a simple Const node in each case,
so in that sense the plans are identical. Again, this is a little bit
academic for the most part.

I highly recommend pretending that the rules system doesn't exist.

--
Peter Geoghegan http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training and Services