Thread: Convert query plan to sql query
Hello, I would like to transform the query plan (output of the planner, debug_print_plan) into an sql query. I know that there are pieces of the query plan that might be machine dependent (in var for example). So I wanted to have your suggestions or thoughts before I put efforts into it. Basically, if I have:query1 -> parser -> rewriter -> planner the process would be :query_plan -> planner -> parser -> query2 query1 and query2 are not necessarily the same due to rewrite, stats.. Thanks! -- View this message in context: http://postgresql.1045698.n5.nabble.com/Convert-query-plan-to-sql-query-tp5825727.html Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.
<div dir="ltr">May be you want to check how it's done in Postgres-XC. Postgres-XC works on plans being created by PostgreSQLand "reverse-engineers" queries (for parts of the plans which are "shippable".) The notions of "shippability" maynot be of interest to you, but the code to "reverse-engineer" most of the plan nodes is there in Postgres-XC.<br /></div><divclass="gmail_extra"><br /><div class="gmail_quote">On Wed, Nov 5, 2014 at 8:47 AM, mariem <span dir="ltr"><<ahref="mailto:mariem.benfadhel@gmail.com" target="_blank">mariem.benfadhel@gmail.com</a>></span> wrote:<br/><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">Hello,<br/><br /> I would like to transform the query plan (output of the planner,<br /> debug_print_plan)into an sql query.<br /> I know that there are pieces of the query plan that might be machine<br /> dependent(in var for example).<br /> So I wanted to have your suggestions or thoughts before I put efforts into<br /> it.<br/><br /> Basically, if I have:<br /> query1 -> parser -> rewriter -> planner<br /> the process would be :<br/> query_plan -> planner -> parser -> query2<br /><br /> query1 and query2 are not necessarily the same dueto rewrite, stats..<br /><br /> Thanks!<br /><br /><br /><br /><br /> --<br /> View this message in context: <a href="http://postgresql.1045698.n5.nabble.com/Convert-query-plan-to-sql-query-tp5825727.html" target="_blank">http://postgresql.1045698.n5.nabble.com/Convert-query-plan-to-sql-query-tp5825727.html</a><br/> Sent fromthe PostgreSQL - hackers mailing list archive at Nabble.com.<br /><span class="HOEnZb"><font color="#888888"><br /><br/> --<br /> Sent via pgsql-hackers mailing list (<a href="mailto:pgsql-hackers@postgresql.org">pgsql-hackers@postgresql.org</a>)<br/> To make changes to your subscription:<br/><a href="http://www.postgresql.org/mailpref/pgsql-hackers" target="_blank">http://www.postgresql.org/mailpref/pgsql-hackers</a><br/></font></span></blockquote></div><br /><br clear="all"/><br />-- <br /><div dir="ltr">Best Wishes,<br />Ashutosh Bapat<br />EnterpriseDB Corporation<br />The PostgresDatabase Company<br /></div></div>
mariem <mariem.benfadhel@gmail.com> wrote: > Hello, > > I would like to transform the query plan (output of the planner, > debug_print_plan) into an sql query. I don't think SQL can express the information the plan contains. For example, join methods (hash, nest loop, merge). -- Antonin Houska Cybertec Schönig & Schönig GmbH Gröhrmühlgasse 26 A-2700 Wiener Neustadt Web: http://www.postgresql-support.de, http://www.cybertec.at
wrote: >I don't think SQL can express the information the plan contains. For example, >join methods (hash, nest loop, merge). I don't need the way the query will be executed, so there is no need for (hash, nest loop, merge). -- View this message in context: http://postgresql.1045698.n5.nabble.com/Convert-query-plan-to-sql-query-tp5825727p5825877.html Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.
>May be you want to check how it's done in Postgres-XC. Postgres-XC works on >plans being created by PostgreSQL and "reverse-engineers" queries (for >parts of the plans which are "shippable".) The notions of "shippability" >may not be of interest to you, but the code to "reverse-engineer" most of >the plan nodes is there in Postgres-XC. I'm glad you used "reverse engineering" that's exactly what I need. I didn't get a chance to look at the internals of Postgres-XC, thank you for the info. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Convert-query-plan-to-sql-query-tp5825727p5825878.html Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.
mariem <mariem.benfadhel@gmail.com> writes:
>> I don't think SQL can express the information the plan contains. For example,
>> join methods (hash, nest loop, merge).
> I don't need the way the query will be executed, so there is no need for
> (hash, nest loop, merge).
If you don't need that, why are you insistent on extracting the
information from a plan tree?
It seems far simpler to me to make use of ruleutils.c to reverse-list
the original parsetree. That functionality already exists and is well
tested and well maintained. If you insist on working from a plan tree,
you will be writing a fair amount of code that you will have to maintain
yourself. And I absolutely, positively guarantee that we will break it
in every major release, and occasionally in minor releases. You should
read the git history of explain.c and ruleutils.c and ask yourself whether
you want to keep up with that level of churn.
regards, tom lane
Hi Tom, >If you don't need that, why are you insistent on extracting the >information from a plan tree? I need to resolve expressions and apply rewrite rules before I reverse the query plan to a query. >It seems far simpler to me to make use of ruleutils.c to reverse-list >the original parsetree. That functionality already exists and is well >tested and well maintained. If you insist on working from a plan tree, >you will be writing a fair amount of code that you will have to maintain >yourself. And I absolutely, positively guarantee that we will break it >in every major release, and occasionally in minor releases. You should >read the git history of explain.c and ruleutils.c and ask yourself whether >you want to keep up with that level of churn. I'm aware of ruleutils.c which I think is a good tool but I don't think it's appropriate as it takes the parse tree as input. Best, Mariem -- View this message in context: http://postgresql.1045698.n5.nabble.com/Convert-query-plan-to-sql-query-tp5825727p5826175.html Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.
On Sat, Nov 8, 2014 at 1:09 AM, mariem <mariem.benfadhel@gmail.com> wrote: > Hi Tom, >>If you don't need that, why are you insistent on extracting the >>information from a plan tree? > > I need to resolve expressions and apply rewrite rules before I reverse the > query plan to a query. > >>It seems far simpler to me to make use of ruleutils.c to reverse-list >>the original parsetree. That functionality already exists and is well >>tested and well maintained. If you insist on working from a plan tree, >>you will be writing a fair amount of code that you will have to maintain >>yourself. And I absolutely, positively guarantee that we will break it >>in every major release, and occasionally in minor releases. You should >>read the git history of explain.c and ruleutils.c and ask yourself whether >>you want to keep up with that level of churn. > > I'm aware of ruleutils.c which I think is a good tool but I don't think it's > appropriate as it takes the parse tree as input. "Parse", "Rewrite", and "Plan" are distinct stages. ruleutils.c takes the tree that results from rewriting, before planning has been done. So I'm not sure it's quite accurate to say that it takes the "parse tree" - rewrite rules will already have been applied. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
Robert Haas <robertmhaas@gmail.com> writes:
> On Sat, Nov 8, 2014 at 1:09 AM, mariem <mariem.benfadhel@gmail.com> wrote:
>> I'm aware of ruleutils.c which I think is a good tool but I don't think it's
>> appropriate as it takes the parse tree as input.
> "Parse", "Rewrite", and "Plan" are distinct stages. ruleutils.c takes
> the tree that results from rewriting, before planning has been done.
> So I'm not sure it's quite accurate to say that it takes the "parse
> tree" - rewrite rules will already have been applied.
More specifically: rewrite is a parsetree-to-parsetree transformation;
it does not change the representational rules at all. It's true that
the "typical" use of ruleutils is on parser output (ie stored views)
but it will work fine on rewriter output.
If what you're wishing for is that you could also capture the effects
of planner steps that are in the nature of source-to-source
transformations, I think that's going to be harder because no great
effort has been made to keep those at arm's length from steps that
don't have results describable as pure SQL. However, you could probably
get pretty far if you applied ruleutils.c to the modified parse tree
after the constant-folding and join tree simplification phases.
regards, tom lane
>If what you're wishing for is that you could also capture the effects >of planner steps that are in the nature of source-to-source >transformations, I think that's going to be harder because no great >effort has been made to keep those at arm's length from steps that >don't have results describable as pure SQL. However, you could probably >get pretty far if you applied ruleutils.c to the modified parse tree >after the constant-folding and join tree simplification phases. I'm not sure if I understand what you mean by source-to-source transformations. But yes, what I'm aiming is applying simplification phases and constant-folding before transforming the query tree back to sql text query. Thank you for the suggestions. Best, Mariem -- View this message in context: http://postgresql.nabble.com/Convert-query-plan-to-sql-query-tp5825727p5826448.html Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.