Обсуждение: Does "explain (QueryTree [RAW| ANAYLZE|REWRITE] | Optimization [PULL_UP_BEFORE| PULL_UP_AFTER|...]) SELECT XXXX" helpful?
Hi all,
When we debugging or hacking the internal, the tedious things is to debug step by step the code, so that make sure the PostgreSQL generates the right raw syntax tree or query tree or the plans. Therefore, i think if we have a uitility statment to show that, such as, we can using the explain statment to ouput the raw syntax tree of a sql or the query tree, etc
I think it would be a helpful tools for hackers to identify what's wrong in the code we have changed because we can show the differents between optimization-beforewards and optimization-afterwards in raw syntax tree, query tree or etc. after performing the pull-up subselect and pull-up subqueries the query tree made changes a lot. If there is a tools to show the diff between them, i think that it's a good thing. So, the extened feautes of explain statement as following maybe a helpful features. Althoug, we can logging the raw tree by using the configuration, but the raw node logs are logged on server, not client. in partice, we perhaps have not rights to access the server, just only though the psql console. based on the reasons i mentioned above, therefore, i think the extended features of explain statments will be help us to do works more convinient.
EXPLAIN (QueryTree [RAW|ANALYZE|REWRITE] | Optimization PULL-UP-BEFORE | PULL-UP-AFTER|...) select xxxx.
And, now i have implemented the Explain (QueryTree Raw) select xxx, to output the raw syntax tree of a query statement. just like as below shown.
explain (QueryTree RAW ) select * from foo, bar;
the output as :
------------------------------------------------------------------------------------------------------------------------------------------------
<explain comments="The Raw Query Tree Node">
{
SELECT
:distinctClause <>
:intoClause <>
:targetList ({RESTARGET :name <> :indirection <> :val {COLUMNREF :fields ({A_STAR}) :location 31} :location 31})
:fromClause ({RANGEVAR :schemaname <> :relname foo :inhOpt 2 :relpersistence p :alias <> :location 38} {RANGEVAR :schemaname <> :reln
ame bar :inhOpt 2 :relpersistence p :alias <> :location 43})
:whereClause <>
:groupClause <>
:havingClause <>
:windowClause <>
:valuesLists <>
:sortClause <>
:limitOffset <>
:limitCount <>
:lockingClause <>
:withClause <>
:op 0
:all false
:larg <>
:rarg <>
}
</explain>
(23 rows)
------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------
<explain comments="The Raw Query Tree Node">
{
SELECT
:distinctClause <>
:intoClause <>
:targetList ({RESTARGET :name <> :indirection <> :val {COLUMNREF :fields ({A_STAR}) :location 31} :location 31})
:fromClause ({RANGEVAR :schemaname <> :relname foo :inhOpt 2 :relpersistence p :alias <> :location 38} {RANGEVAR :schemaname <> :reln
ame bar :inhOpt 2 :relpersistence p :alias <> :location 43})
:whereClause <>
:groupClause <>
:havingClause <>
:windowClause <>
:valuesLists <>
:sortClause <>
:limitOffset <>
:limitCount <>
:lockingClause <>
:withClause <>
:op 0
:all false
:larg <>
:rarg <>
}
</explain>
(23 rows)
------------------------------------------------------------------------------------------------------------------------------------------------
Best Regards,
RingsC.
Hao Lee <mixtrue@gmail.com> writes:
> When we debugging or hacking the internal, the tedious things is to
> debug step by step the code, so that make sure the PostgreSQL generates the
> right raw syntax tree or query tree or the plans. Therefore, i think if we
> have a uitility statment to show that, such as, we can using the explain
> statment to ouput the raw syntax tree of a sql or the query tree, etc
FWIW, I usually just insert pprint() calls at appropriate places (or
even more usually, call it from gdb after setting a breakpoint somewhere).
The places where you want to see a node tree are varied and unpredictable,
so I rather doubt that a utility statement of this sort would be very
helpful.
See also the debug_print_parse, debug_print_rewritten, debug_print_plan
GUCs, which already do more or less what you're suggesting.
All of the above end up dumping into the postmaster log, not to the
client, but IME that's about as convenient if not more so. You'd be
putting the output into a file anyway.
regards, tom lane