Re: An Idea for planner hints - Mailing list pgsql-hackers

On Wed, Aug 23, 2006 at 08:42:10AM -0700, Mark Dilger wrote:
> Jim C. Nasby wrote:
> >On Tue, Aug 22, 2006 at 11:56:17AM -0700, Mark Dilger wrote:
> >>I proposed something like this quite a bit up-thread.  I was hoping we 
> >>could have a mode in which the system would run the second, third, 
> >>fourth, ... best plans rather than just the best looking one, and then 
> >>determine from actual runtime statistics which was best.  (The proposal 
> >>also included the ability to output the best plan and read that in at a 
> >>later time in lieu of a SQL query, but that part of it can be ignored if 
> >>you like.)  The posting didn't generate much response, so I'm not sure 
> >>what people thought of it.  The only major problem I see is getting the 
> >>planner to keep track of alternate plans.  I don't know the internals of 
> >>it very well, but I think the genetic query optimizer doesn't have a 
> >>concept of "runner-up #1", "runner-up #2", etc., which it would need to 
> >>have.
> >
> >I think the biggest issue is that you'd have to account for varying load
> >on the box. If we assume that the database is the only thing running on
> >the box, we might be able to do that by looking at things like how much
> >IO traffic we generated (though of course OS caching will screw with
> >that).
> >
> >Actually, that's another issue... any plans run after the first one will
> >show up as being artificially fast, since there will be a lot of extra
> >cached data.
> 
> Yes, caching issues prevent you from using wall-clock time.  We could 
> instrument the code to count the number of rows vs. the number predicted 
> for each internal join, from which new cost estimates could be generated.
But if you're only looking at the number of rows, I suspect there's no
need to actually run the other plans; you can just look at how many rows
you got in the plan you used. Worst-case, you may have to figure out the
correlation stats for the result-set, which could probably be done on
the fly without too much impact.

> Perhaps you can check my reasoning for me:  I'm imagining a query which 
> computes AxBxCxD, where A, B, C, and D are actual tables.  I'm also 
> imagining that the planner always chooses AxB first, then joins on C, then 
> joins on D.  (It does so because the single-table statistics suggest this 
> as the best course of action.) It might be that AxD is a really small 
>  metatable, much smaller than would be estimated from the statistics for A 
> independent of the statistics for D, but AxB is pretty much what you would 
> expect given the independent statistics for A and B.  So we need some way 
> for the system to stumble upon that fact.  If we only ever calculate 
> cross-join statistics for plans that the system chooses, we will only 
> discover that AxB is about the size we expected it to be.  So, if the 
> actual size of AxB is nearly equal to the estimated size of AxB, the system 
> will continue to choose the same plan in future queries, totally ignorant 
> of the advantages of doing AxD first.

Is there actually evidence that there's a lot of problems with bad join
orders? ISTM that's one of the areas where the planner actually does a
pretty good job.

> That last paragraph is my reasoning for suggesting that the system have a 
> mode in which it runs the "runner-up #1", "runner-up #2", etc sorts of 
> plans.  Such a mode could force it down alternate paths where it might pick 
> up interesting statistics that it wouldn't find otherwise.
> 
> This idea could be changed somewhat.  Rather than running the other plans, 
> we could just extract from them which alternate joins they include, and 
> consider also calculating those join statistics.
> 
> mark
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
> 
>               http://www.postgresql.org/docs/faq
> 

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