[GENERAL] contrib/plantuner - enable PostgreSQL planner hints
Hi there, this is an announcement of our new contribution module for PostgreSQL - Plantuner - enable planner hints (http://www.sai.msu.su/~megera/wiki/plantuner). Example: =# LOAD 'plantuner'; =# create table test(id int); =# create index id_idx on test(id); =# create index id_idx2 on test(id); =# \d test Table "public.test" Column | Type | Modifiers +-+--- id | integer | Indexes: "id_idx" btree (id) "id_idx2" btree (id) =# explain select id from test where id=1; QUERY PLAN --- Bitmap Heap Scan on test (cost=4.34..15.03 rows=12 width=4) Recheck Cond: (id = 1) -> Bitmap Index Scan on id_idx2 (cost=0.00..4.34 rows=12 width=0) Index Cond: (id = 1) (4 rows) =# set enable_seqscan=off; =# set plantuner.forbid_index='id_idx2'; =# explain select id from test where id=1; QUERY PLAN -- Bitmap Heap Scan on test (cost=4.34..15.03 rows=12 width=4) Recheck Cond: (id = 1) -> Bitmap Index Scan on id_idx (cost=0.00..4.34 rows=12 width=0) Index Cond: (id = 1) (4 rows) =# set plantuner.forbid_index='id_idx2,id_idx'; =# explain select id from test where id=1; QUERY PLAN - Seq Scan on test (cost=100.00..140.00 rows=12 width=4) Filter: (id = 1) (2 rows) Regards, Oleg _ Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), Sternberg Astronomical Institute, Moscow University, Russia Internet: o...@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(495)939-16-83, +007(495)939-23-83 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] contrib/plantuner - enable PostgreSQL planner hints
On Oct 6, 2009, at 2:57 PM, Oleg Bartunov wrote: this is an announcement of our new contribution module for PostgreSQL - Plantuner - enable planner hints (http://www.sai.msu.su/~megera/wiki/plantuner). =# set enable_seqscan=off; =# set plantuner.forbid_index='id_idx2'; Out of curiosity, did you look at doing hints as comments in a query? I'm guessing you couldn't actually do that in just a contrib module, but it's how Oracle handles hints, and it seems to be *much* more convenient, because a hint only applies for a specific query. I think it's extremely unlikely you would intentionally want the same hint to apply to a bunch of queries, and extremely likely that you could accidentally forget to re-enable something. That said, thanks for contributing this! -- Decibel!, aka Jim C. Nasby, Database Architect deci...@decibel.org Give your computer some brain candy! www.distributed.net Team #1828 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] contrib/plantuner - enable PostgreSQL planner hints
On Oct 16, 2009, at 10:04 AM, decibel wrote: Out of curiosity, did you look at doing hints as comments in a query? I don't think that a contrib module could change the grammar. -- -- Christophe Pettus x...@thebuild.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] contrib/plantuner - enable PostgreSQL planner hints
On Fri, Oct 16, 2009 at 10:04 AM, decibel wrote: > Out of curiosity, did you look at doing hints as comments in a query? I'm > guessing you couldn't actually do that in just a contrib module, but it's > how Oracle handles hints, and it seems to be *much* more convenient, because > a hint only applies for a specific query Fwiw Oracle is moving away from this now. At OpenWorld the optimizer folks were pleading with folks to get rid of all their hard-coded hints by preparing plans for hinted queries and loading those as the approved plans. In 11g there's a system which ensures the database will not run any plan that isn't approved. In fact it looks an *awful* lot like the system I sketched out 6 years ago in: http://article.gmane.org/gmane.comp.db.postgresql.devel.general/11385 -- greg -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [HACKERS] [GENERAL] contrib/plantuner - enable PostgreSQL planner hints
On Fri, 2009-10-16 at 12:04 -0500, decibel wrote: > I'm guessing you couldn't actually do that in just a contrib module, > but it's how Oracle handles hints, and it seems to be *much* more > convenient, because a hint only applies for a specific query. If that's the only reason, that seems easy enough to solve by using SET right before the query. SET LOCAL might be convenient if you want to forget the setting after the query. Connection pool software will do a RESET ALL anyway. There are reasons that it might be convenient to use hints inside the query itself -- for instance, if you want something to apply only to a subquery. I'm still hoping that someone will come up with a more elegant solution to solve that problem though. Regards, Jeff Davis -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
