Thread: How to construct an exact plan
Hi, I have developed a new operators, and I want to do some tests on it. I do not want the optimizer to choose the plan for me, and I need to construct a plan as exact as I want. Can anyone provide me a way to achieve that? Thanks -- Pei
I forgot to mention that I am using postgresql 8.2.5. Thanks -- Pei On Mon, Aug 30, 2010 at 1:34 PM, Pei He <hepeimail@gmail.com> wrote: > Hi, > I have developed a new operators, and I want to do some tests on it. > I do not want the optimizer to choose the plan for me, and I need to > construct a plan as exact as I want. > > Can anyone provide me a way to achieve that? > > Thanks > -- > Pei >
Hi, I am hacking postgresql 8.2.5. a) and b) do not work for me. The situation is that I made a join operator, and a scan operator. And, The join operator requires the scan operator as the inner. So, I need to have the full control of the join plan. I am not ready to provide the optimization support for the two new operators. And, I want to run some performance tests before working on the optimization part. So, I want to know if it is possible to directly create a path or a plan, and do a unit test for the operators. Thanks -- Pei On Mon, Aug 30, 2010 at 1:59 PM, Josh Berkus <josh@agliodbs.com> wrote: > >> I have developed a new operators, and I want to do some tests on it. >> I do not want the optimizer to choose the plan for me, and I need to >> construct a plan as exact as I want. >> >> Can anyone provide me a way to achieve that? > > a) easy: choose a simple enough query that its plan is always predictable. > > b) moderate: choose a query whose plan is predictable if you manipulate > the enable_* configuration settings > > c) hard: hack the PostgreSQL planner to choose a specific execution > plan, and recompile Postgres. > > -- > -- Josh Berkus > PostgreSQL Experts Inc. > http://www.pgexperts.com >
2010/8/30 Pei He <hepeimail@gmail.com>: > Hi, > I am hacking postgresql 8.2.5. a) and b) do not work for me. > > The situation is that I made a join operator, and a scan operator. > And, The join operator requires the scan operator as the inner. So, I > need to have the full control of the join plan. > > I am not ready to provide the optimization support for the two new > operators. And, I want to run some performance tests before working on > the optimization part. > > So, I want to know if it is possible to directly create a path or a > plan, and do a unit test for the operators. > yes, it is possible - but it isn't simple. I thing, so better is simple implementation of all parts and then runtime blocking some (for you not interesting) buildin methods via SET enable_.... to off. Regards Pavel Stehule > > Thanks > -- > Pei > > On Mon, Aug 30, 2010 at 1:59 PM, Josh Berkus <josh@agliodbs.com> wrote: >> >>> I have developed a new operators, and I want to do some tests on it. >>> I do not want the optimizer to choose the plan for me, and I need to >>> construct a plan as exact as I want. >>> >>> Can anyone provide me a way to achieve that? >> >> a) easy: choose a simple enough query that its plan is always predictable. >> >> b) moderate: choose a query whose plan is predictable if you manipulate >> the enable_* configuration settings >> >> c) hard: hack the PostgreSQL planner to choose a specific execution >> plan, and recompile Postgres. >> >> -- >> -- Josh Berkus >> PostgreSQL Experts Inc. >> http://www.pgexperts.com >> > > -- > Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-hackers >
hello ... here is the ultimate revelation of planner hints in postgres ... let us praise oleg and teodor for solving a practical problem for practical people ... http://www.sai.msu.su/~megera/wiki/plantuner try this one ... it is excellent and definitely helpful for many many people out there. unfortunately this code is not too well known. many thanks, hans On Aug 30, 2010, at 9:41 PM, Pavel Stehule wrote: > 2010/8/30 Pei He <hepeimail@gmail.com>: >> Hi, >> I am hacking postgresql 8.2.5. a) and b) do not work for me. >> >> The situation is that I made a join operator, and a scan operator. >> And, The join operator requires the scan operator as the inner. So, I >> need to have the full control of the join plan. >> >> I am not ready to provide the optimization support for the two new >> operators. And, I want to run some performance tests before working on >> the optimization part. >> >> So, I want to know if it is possible to directly create a path or a >> plan, and do a unit test for the operators. >> > > yes, it is possible - but it isn't simple. I thing, so better is > simple implementation of all parts and then runtime blocking some (for > you not interesting) buildin methods via SET enable_.... to off. > > Regards > > Pavel Stehule > >> >> Thanks >> -- >> Pei >> >> On Mon, Aug 30, 2010 at 1:59 PM, Josh Berkus <josh@agliodbs.com> wrote: >>> >>>> I have developed a new operators, and I want to do some tests on it. >>>> I do not want the optimizer to choose the plan for me, and I need to >>>> construct a plan as exact as I want. >>>> >>>> Can anyone provide me a way to achieve that? >>> >>> a) easy: choose a simple enough query that its plan is always predictable. >>> >>> b) moderate: choose a query whose plan is predictable if you manipulate >>> the enable_* configuration settings >>> >>> c) hard: hack the PostgreSQL planner to choose a specific execution >>> plan, and recompile Postgres. >>> >>> -- >>> -- Josh Berkus >>> PostgreSQL Experts Inc. >>> http://www.pgexperts.com >>> >> >> -- >> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) >> To make changes to your subscription: >> http://www.postgresql.org/mailpref/pgsql-hackers >> > > -- > Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-hackers > -- Cybertec Schönig & Schönig GmbH Gröhrmühlgasse 26 A-2700 Wiener Neustadt, Austria Web: http://www.postgresql-support.de