Re: EXPLAIN omits schema?

"Magnus Hagander" <magnus(at)hagander(dot)net> writes:

> Assuming you can actually *represent* the whole plan as tables, that would
> of course work fine. But I assume you mean "virtual tables"?

Are you saying there are data structures relational databases aren't good at
representing?

In Oracle you had to run explain and the go run a query against your
plan_table to generate a text report of the plan. It was kind of cumbersome in
the usual case but it's very convenient for gui tools which can then perform
different queries than users would run from the command-line client.

The main advantage of using real tables is that you can then let your
application run unchanged and go look at the plans that it generated from
another connection. The plan it generated may well be different from what you
would get if you tried to run the same query later in a different connection.

It also means you could go query for things like "what query performed the
largest disk sort" or "what is the average cost/millisecond ratio" or "which
query nodes had the largest and smallest expected-rows/actual rows ratio" etc.

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com