Re: [HACKERS] Convert query plan to sql query
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. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Convert query plan to sql query
On Sat, Nov 8, 2014 at 1:09 AM, mariem mariem.benfad...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Convert query plan to sql query
Robert Haas robertmh...@gmail.com writes: On Sat, Nov 8, 2014 at 1:09 AM, mariem mariem.benfad...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Convert query plan to sql query
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. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Convert query plan to sql query
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. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Convert query plan to sql query
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. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Convert query plan to sql query
mariem mariem.benfad...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] 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. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Convert query plan to sql query
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.benfad...@gmail.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@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers -- Best Wishes, Ashutosh Bapat EnterpriseDB Corporation The Postgres Database Company
Re: [HACKERS] Convert query plan to sql query
mariem mariem.benfad...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
