On Fri, Dec 02, 2005 at 11:07:06AM +0100, Csaba Nagy wrote: > So for me the "hint" mechanism is good for telling the server that I'm > not interested at all in the BEST plan but which risks getting very bad > on occasions, but in a good enough plan which is safe.
I'm wondering if long term another approach might be to have another parameter in the planner, cost_error or selectivity_error which is an indication of how accurate we think it is. So for example you have an index scan might cost x but with a possible error of 15% and the seqscan might cost y but with an error of 1%. The "error" for nested loop would be the product of the two inputs, whereas a merge join whould be much less sensetive to error. A sort or hash join would react badly to large variations of input. So in cases where there is a choice between two indexscans with one slightly more expensive and more accurate but can result in a mergejoin would be a better choice than a possibly highly selective index but without accurate info that needs to be fed into a nested loop. Even though the latter might look better, the former is the "safer" option. I think this would solve the problem where people see sudden flip-flops between good and bad plans. The downside is that it's yet another parameter for the planner to get wrong. Unfortunatly, this is the kind of thing people write thesises on and I don't think many people have the grounding in statistics to make it all work. Have a ncie day, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a > tool for doing 5% of the work and then sitting around waiting for someone > else to do the other 95% so you can sue them.
pgpZhqhRS8A1y.pgp
Description: PGP signature
