Thread: Forcing Postgres to Execute a Specific Plan
I'm doing some performance experiments with postgres (8.3.1) and would like to force postgres to execute a particular query plan. Is there a straightforward way to specify a query plan to postgres either interactively or programatically? Thanks. John Cieslewicz.
Not really. It was decided long ago that in that way madness lies. OTOH, there are ways to tune the behaviour through changes to random_page_cose, cpu_xxx_cost and effective_cache_size settings. Then there's the mallet to the forebrain that are the set enable_nestloop=off type settings. They work, but they shouldn't be your first line of attack so much as a troubleshooting tool to figure out what pgsql might be getting wrong. On Mon, Jun 2, 2008 at 1:43 PM, John Cieslewicz <johnc@cs.columbia.edu> wrote: > I'm doing some performance experiments with postgres (8.3.1) and would like > to force postgres to execute a particular query plan. Is there a > straightforward way to specify a query plan to postgres either interactively > or programatically? > > Thanks. > > John Cieslewicz. > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general >
I completely understand that what I am proposing is somewhat mad and I didn't expect it to be easy. Basically, I'm doing some research on a new operator and would like to start testing it by inserting it into a very specific place in very specific plans without having to do too much work in plan generation or optimization. I think that I could do this by writing some code to inspect a plan and swap out the piece that I care about. I realize this is a hack, but at the moment it's just for research purposes. Though I have worked with the internals of other db systems, I'm still getting familiar with postgres. Could such a piece of code be placed in the optimizer just before it returns an optimized plan or can a plan be modified after it is returned by the optimizer? John Cieslewicz. On Jun 2, 2008, at 5:24 PM, Scott Marlowe wrote: > Not really. It was decided long ago that in that way madness lies. > > OTOH, there are ways to tune the behaviour through changes to > random_page_cose, cpu_xxx_cost and effective_cache_size settings. > > Then there's the mallet to the forebrain that are the set > enable_nestloop=off type settings. They work, but they shouldn't be > your first line of attack so much as a troubleshooting tool to figure > out what pgsql might be getting wrong. > > On Mon, Jun 2, 2008 at 1:43 PM, John Cieslewicz > <johnc@cs.columbia.edu> wrote: >> I'm doing some performance experiments with postgres (8.3.1) and >> would like >> to force postgres to execute a particular query plan. Is there a >> straightforward way to specify a query plan to postgres either >> interactively >> or programatically? >> >> Thanks. >> >> John Cieslewicz. >> >> -- >> Sent via pgsql-general mailing list (pgsql-general@postgresql.org) >> To make changes to your subscription: >> http://www.postgresql.org/mailpref/pgsql-general >>
All paths of optimizer are just in function "standard_planner", which mainly calls "subquery_planner", which just takes the rewrited structure "Query" as the main parameter. But system provides another way if you wannt to write your own optimizer, that is: define the global var "planner_hook" to your own optimizer function (please refer to function "planner"). So this is one of the way prevents the system takes it own optimizer routine.
If you want to modify the plan returned by the optimizer, you can add some code just in the function "planner", i.e., takes result as the param of your routine.
Any way, It is needed that you get very familiar with the structure of "PlannedStmt".
**********************************************************************
2008/6/3 John Cieslewicz <johnc@cs.columbia.edu>:
I completely understand that what I am proposing is somewhat mad and I didn't expect it to be easy.
Basically, I'm doing some research on a new operator and would like to start testing it by inserting it into a very specific place in very specific plans without having to do too much work in plan generation or optimization. I think that I could do this by writing some code to inspect a plan and swap out the piece that I care about. I realize this is a hack, but at the moment it's just for research purposes. Though I have worked with the internals of other db systems, I'm still getting familiar with postgres. Could such a piece of code be placed in the optimizer just before it returns an optimized plan or can a plan be modified after it is returned by the optimizer?
John Cieslewicz.
If you want to modify the plan returned by the optimizer, you can add some code just in the function "planner", i.e., takes result as the param of your routine.
Any way, It is needed that you get very familiar with the structure of "PlannedStmt".
**********************************************************************
2008/6/3 John Cieslewicz <johnc@cs.columbia.edu>:
I completely understand that what I am proposing is somewhat mad and I didn't expect it to be easy.
Basically, I'm doing some research on a new operator and would like to start testing it by inserting it into a very specific place in very specific plans without having to do too much work in plan generation or optimization. I think that I could do this by writing some code to inspect a plan and swap out the piece that I care about. I realize this is a hack, but at the moment it's just for research purposes. Though I have worked with the internals of other db systems, I'm still getting familiar with postgres. Could such a piece of code be placed in the optimizer just before it returns an optimized plan or can a plan be modified after it is returned by the optimizer?
John Cieslewicz.