Andrew Sullivan wrote: > Given the apparent infrequency of docs-consultation, I am > considerably less sanguine than you are about the correctness of the > choices many DBAs make. Poking at the planner to make it use an > index more often strikes me as at least as likely to cause worse > performance.
I disagree :-) > > > I don't think you can solve this with statistics. It is a far more > > complex problem than that. > > Aw, you just need to take more stats courses ;) You need to move a away form the view that everything calculable and deterministic and move over to the more chaotic perspective where "more likely than not" is about the best one can hope for. The cost based optimizer is just such a system. There are so many things that can affect the performance of a query that there is no way to adequately model them. Disk performance, inner/outer tracks, RAID systems, concurrent system activity, and so on. Look at the pgbench utility. I can't run that program without a +- 10% variation from run to run, no mater how many times I run vacuum and checkpoint. When the estimated cost ranges of the different planner strategies overlap, I think that is a case where two approximations with indeterminate precision must be evaluated. In such cases, the variance between the numbers have little or no absolute relevance to one another. This is where heuristics and a bit of fuzziness needs to be applied. Favoring an index scan over a sequential scan would probably generate a better query. ---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster
