On Fri, Oct 14, 2005 at 02:37:37PM -0400, Tom Lane wrote: > "Kevin Grittner" <[EMAIL PROTECTED]> writes: > > I propose capturing only three values from the output of explain > > analyze, and saving it with many columns of context information. > > You really have to capture the rowcounts (est and actual) too. > Otherwise you can't tell if it's a costing problem or a statistics > problem. > > More generally, I think that depending entirely on EXPLAIN ANALYZE > numbers is a bad idea, because the overhead of EXPLAIN ANALYZE is both > significant and variable depending on the plan structure. The numbers > that I think we must capture are the top-level EXPLAIN cost and the > actual runtime of the query (*without* EXPLAIN). Those are the things > we would like to get to track closely. EXPLAIN ANALYZE is incredibly > valuable as context for such numbers, but it's not the thing we actually > wish to optimize.
The problem with that is that we lose all data on per-node costs versus what the planner thought should happen. ISTM it would be better to run all 3 scenarios: explain, explain analyze, and select count(*). As for the caching issue that raises, I don't buy into the theory that all testing should be done with nothing in the cache, because it's entirely unrealistic in most cases. I think what makes a lot more sense is to do two runs, clearing the cache and swapping the order of analyze and count(*) between the two. That would give us a complete set of data: not only would we know how things break down at a node-by-note level, we'd also know how caching affected things. Given some clever data-mining, one could probably even produce cost estimates for the overhead of explain analyze which could be factored into further analysis. Of course the one downside is this doubles the amount of time it takes for a test to run... -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---------------------------(end of broadcast)--------------------------- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match