Thread: Parsing of VIEW definitions

I'm just curious...

If you have a view:
    create foo_view as select * from foo;

Then you query it:
    select * from foo_view;

What, behind the scenes, actually happens?

Is the definition of "foo_view" looked up in the system catalog, query
rewritten, parsed, then executed?

In other words, does the query *literally* get rewritten as:
    select * from (select * from foo) as foo;
before it is parsed?

eric

"Eric B.Ridge" <ebr@tcdi.com> writes:
> I'm just curious...
> If you have a view:
>     create foo_view as select * from foo;
> Then you query it:
>     select * from foo_view;

> What, behind the scenes, actually happens?

There's a pretty good description in the Programmer's Guide:
http://www.ca.postgresql.org/users-lounge/docs/7.3/postgres/rules.html

After reading that you can try looking at it for yourself.  For example:

regression=# create table foo (f1 int, f2 text);
CREATE TABLE
regression=# create view foo_view as select * from foo;
CREATE VIEW
regression=# select ev_action from pg_rewrite
regression-# where ev_class = 'foo_view'::regclass; -- before 7.3 use a join

In CVS tip I get:

 ({QUERY :commandType 1 :querySource 0 :utilityStmt <> :resultRelation 0 :into <> :isPortal false :isBinary false
:hasAggsfalse :hasSubLinks false :rtable ({RTE :alias {ALIAS :aliasname *OLD* :colnames <>} :eref {ALIAS :aliasname
*OLD*:colnames ("f1" "f2")} :rtekind 0 :relid 2581139 :inh false :inFromCl false :checkForRead false :checkForWrite
false:checkAsUser 1} {RTE :alias {ALIAS :aliasname *NEW* :colnames <>} :eref {ALIAS :aliasname *NEW* :colnames ("f1"
"f2")}:rtekind 0 :relid 2581139 :inh false :inFromCl false :checkForRead false :checkForWrite false :checkAsUser 1}
{RTE:alias <> :eref {ALIAS :aliasname foo :colnames ("f1" "f2")} :rtekind 0 :relid 2581134 :inh true :inFromCl true
:checkForReadtrue :checkForWrite false :checkAsUser 1}) :jointree {FROMEXPR :fromlist ({RANGETBLREF :rtindex 3}) :quals
<>}:rowMarks () :targetList ({TARGETENTRY :resdom {RESDOM :resno 1 :restype 23 :restypmod -1 :resname f1
:ressortgroupref0 :reskey 0 :reskeyop 0 :resjunk false} :expr! 
 {VAR :varno 3 :varattno 1 :vartype 23 :vartypmod -1 :varlevelsup 0 :varnoold 3 :varoattno 1}} {TARGETENTRY :resdom
{RESDOM:resno 2 :restype 25 :restypmod -1 :resname f2 :ressortgroupref 0 :reskey 0 :reskeyop 0 :resjunk false} :expr
{VAR:varno 3 :varattno 2 :vartype 25 :vartypmod -1 :varlevelsup 0 :varnoold 3 :varoattno 2}}) :groupClause <>
:havingQual<> :distinctClause <> :sortClause <> :limitOffset <> :limitCount <> :setOperations <> :resultRelations ()}) 

which is a flattened textual representation of the internal querytree
data structure that represents the SELECT.  The above-cited
documentation would give you a rough idea about the parts of this data
structure, and you can find the details in src/include/nodes/*.h
(mostly primnodes.h and parsenodes.h for stuff appearing in a Query
tree).

> Is the definition of "foo_view" looked up in the system catalog, query
> rewritten, parsed, then executed?

The Query representation is rebuilt from this textual form and then
substituted into the referencing query.  I wouldn't call that "parsing",
though --- the work of the parser phase is long over.  See
http://www.ca.postgresql.org/users-lounge/docs/7.3/postgres/overview.html

> In other words, does the query *literally* get rewritten as:
>     select * from (select * from foo) as foo;
> before it is parsed?

Substituting "planned" for "parsed", yes, pretty nearly.  What the
planner sees is practically indistinguishable from what it sees if you
write out the view's definition as a sub-select like that.  Exercise for
the student: turn on DEBUG_PRINT_REWRITTEN and compare the post-rewrite
query trees for the two cases.  (Also set DEBUG_PRETTY_PRINT to make the
trees dumped into the postmaster log easier to read.)

            regards, tom lane

On Saturday, February 8, 2003, at 11:54  AM, Tom Lane wrote:
> "Eric B.Ridge" <ebr@tcdi.com> writes:
>> I'm just curious...
>> If you have a view:

<snip>

>> What, behind the scenes, actually happens?
>
> There's a pretty good description in the Programmer's Guide:
> http://www.ca.postgresql.org/users-lounge/docs/7.3/postgres/rules.html
>
> After reading that you can try looking at it for yourself.  For
> example:

Tom, thanks for explaining this.  Your explanation and the docs really
cleared things up.  But I still have a few questions...

<snip>

>> Is the definition of "foo_view" looked up in the system catalog, query
>> rewritten, parsed, then executed?
>
> The Query representation is rebuilt from this textual form and then
> substituted into the referencing query.  I wouldn't call that
> "parsing",

So the text form of the Query *is* looked up in pg_rewrite on every
use?  Or is it looked up only once (per backend maybe?)?  The docs
didn't say... unless I overlooked it.

> though --- the work of the parser phase is long over.  See
> http://www.ca.postgresql.org/users-lounge/docs/7.3/postgres/
> overview.html

<snip>

> What the planner sees is practically indistinguishable from what it
> sees if you
> write out the view's definition as a sub-select like that.  Exercise
> for

So what should be more efficient?  Re-parsing/planning the entire SQL
string (w/ the "view" definition in-line) or actually using the view?
How much overhead is really involved in getting the "textual form" and
turning it into a Query?

I guess my real question is (from a pure performance standpoint):  If
I'm trying to trim every last millisecond off query execution time,
should I be using views?

thanks!

eric

"Eric B. Ridge" <ebr@tcdi.com> writes:
> So the text form of the Query *is* looked up in pg_rewrite on every
> use?  Or is it looked up only once (per backend maybe?)?  The docs
> didn't say... unless I overlooked it.

I think it's kept in the relcache, so in typical cases it'd be read
only once per backend.

> So what should be more efficient?  Re-parsing/planning the entire SQL
> string (w/ the "view" definition in-line) or actually using the view?

Planning cost will be the same either way.  Parsing costs should be
less, but on the other hand the rewriter will take some extra cycles.
My guess is it'd be about a wash --- but I've never tried to measure.

            regards, tom lane