Re: An Idea for planner hints

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.
--
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