Re: An Idea for planner hints

Been suggested before... the problem is actually doing something useful
with all that data that's collected, as well as how to collect it
without greatly impacting the system.

On Tue, Aug 08, 2006 at 08:23:05AM -0700, Mark Dilger wrote:
> If this feature I'm proposing already exists, sorry for the waste of
> bandwidth, and could someone please point me to it? :)
>
> What if there were a mode that told postgres to do an exhaustive search (or
> if not exhaustive, then much more extensive search) of all plans (or many
> plans), trying each plan, reporting the performance of each, and discarding
> the query results, much like "explain analyze" does. Postgres could then
> dump the best plan in machine readable (and semi-human readable) form which
> the planner could parse and use at some later date in lieu of a SQL query.
>
> This would allow people with reasonably static table statistics (where the
> best plan is not likely to change) to spend upfront cycles investigating
> the best plan and then embed that plan in their business logic. Since the
> stored plan is both written-by and read-by postgres, it can get quite
> complicated without putting a burden on humans to read and write such
> complicated things. It would also remove the risk that the planner will
> occasionally (due to its nondeterministic workings) choose a really bad
> plan and stall a production system.
>
> mark
>
> Florian G. Pflug wrote:
> >Hi
> >
> >Since the discussion about how to force a specific plan has
> >come up, I though I'd post an idea I had for this a while ago.
> >It's not reall well though out yet, but anyway.
> >
> >When the topic of optimizer hints comes up, people often suggest
> >that there should be a way to force postgres to use a certain
> >index, or do joins in a certain order. AFAIK, this mimics what
> >oracle does - you can put comments into your query that specify
> >what index to use. This approach has two major drawbacks
> >.) Plans that seem good now might not seem that good a few months
> >later - your data might have changed, and other execution plans
> >might fit better now
> >.) You have to change all your queries to make use of features
> >in new postgres versions, like bitmap scans.
> >
> >My experience with the postgres optimizer is that it usually performs
> >great - and if it doesn't, that always boiled down to two problems
> >(at least for me)
> >.) The query is autogenerated, and includes complex, and highly inter-
> >dependent where (or join) conditions. This leads to wrong estimates
> >of where selectivity, and thus to bad plans.
> >.) There are correlations between columns and/or tables that postgres
> >doesn't know about (and has no chance of knowing about). Again, this
> >leads to vastly wrong estimates of row counts, and to bad plans.
> >
> >I think that those bad estimates of the selectivity of where-clauses
> >(or on-clauses for joins) is where postgres could use hints.
> >
> >Image a query like "select ... from t1 join t2 on t1.t2_id = t2.id and
> ><expr>". Lets say that "<expr>" is true for only 1% of the rows in t2 -
> >but those are exactly the rows that have matching rows in t1.
> >
> >Postgres would probably guess that this join will produce about 1/100
> >of the rows that t1 has - but I _know_ that it will produce 100 (!)
> >times more rows.
> >
> >Now, I'd like to hand that information to postgres. I wouldn't want
> >to force any particular access method or join order, but rather I'd
> >just tell it "hey, this expression has selectivity 1 in this context,
> >not 0.01 as you might think".
> >
> >Could that work?
> >
> >greetings, Florian Pflug
> >
> >---------------------------(end of broadcast)---------------------------
> >TIP 1: if posting/reading through Usenet, please send an appropriate
> > subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> > message can get through to the mailing list cleanly
> >
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>

--
Jim C. Nasby, Sr. Engineering Consultant jnasby(at)pervasive(dot)com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461