Re: 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.

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.

On Wed, Nov 5, 2014 at 8:47 AM, mariem <mariem(dot)benfadhel(at)gmail(dot)com> wrote:

> 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.
>
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>

--
Best Wishes,
Ashutosh Bapat
EnterpriseDB Corporation
The Postgres Database Company

mariem <mariem(dot)benfadhel(at)gmail(dot)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(dot)benfadhel(at)gmail(dot)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(dot)benfadhel(at)gmail(dot)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(at)gmail(dot)com> writes:
> On Sat, Nov 8, 2014 at 1:09 AM, mariem <mariem(dot)benfadhel(at)gmail(dot)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.