Re: Access to postgresql query optimizer output

On 10/29/2012 04:07 PM, Seref Arikan wrote:
> Greetings,
> I keep seeing statements like "Postgresql optimizer rewrites this query
> as...." What I'm curious about is, is there a way to obtain SQL form of
> the re-written queries somewhere in the chain of query evaluation? It
> does not make a lot sense to generate sql again, but it would help a lot
> to see the queries in their rewritten form.

I've never seen such a tool, and I'm not sure one is possible, though I
agree it'd be interesting.

The same SQL can result in many different query plans, and there are
many different SQL statements that can all result in the same query
plan. How would you unambiguously show what plan the SQL represented?

It's not so much that Pg's planner "rewrites" one SQL statement to
another. Usually, it's that two or more different SQL statements
optimize down to the same query plan.

EXPLAIN and EXPLAIN ANALYZE show the query plans, and I'm not really
sure you can go backwards from there to SQL in any consistent and
logical way.

--
Craig Ringer