Обсуждение: 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=10000000000.00..10000000040.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: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(495)939-16-83, +007(495)939-23-83
Re: [HACKERS] contrib/plantuner - enable PostgreSQL planner hints
От
Hans-Juergen Schoenig -- PostgreSQL
Дата:
hi there ... for this work i will include you in my evening prayers for at least one week. i know there has been a lot of discussion about this but what you just posted it excellent and more important: USEFUL to many people. i had something else in mind recently as well: virtual indexes. it would help people to decide whether and index would make sense if it would actually exist. in some cases this would make sense as well as many datasets are just to big to try out if an index help.s if there was a vote whether this should be in contrib or in core: +999 from me ... many thanks, hans Oleg Bartunov wrote: > 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=10000000000.00..10000000040.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: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ > phone: +007(495)939-16-83, +007(495)939-23-83 > -- Cybertec Schoenig & Schoenig GmbH Reyergasse 9 / 2 A-2700 Wiener Neustadt Web: www.postgresql-support.de
On Mon, Oct 12, 2009 at 9:20 AM, Hans-Juergen Schoenig -- PostgreSQL <postgres@cybertec.at> wrote: > if there was a vote whether this should be in contrib or in core: +999 from > me ... Well, contrib is a good place to start. It sets a clearly defined ownership / maintenance person, and if the core of pgsql changes, instead of the core hackers having to make this code happy, that job then falls to the original contributor or whoever is willing to keep it fed and working. I think I'm gonna grab a copy and play with it, it sure could help in the cases where the query planner just can't make the right decision (correlation of indexes etc) Thanks to Oleg for this a bunch.
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 decibel@decibel.org Give your computer some brain candy! www.distributed.net Team #1828
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 xof@thebuild.com
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
On Fri, Oct 16, 2009 at 10:04 AM, decibel <decibel@decibel.org> 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