On Tue, Aug 15, 2006 at 07:55:28PM +0200, Peter Eisentraut wrote: > Jim C. Nasby wrote: > > > Meet EXPLAIN ANALYZE. > > > > Which does no good for apps that you don't control the code on. Even > > if you do control the code, you have to find a way to stick EXPLAIN > > ANALYZE in front of every query, and figure out how to deal with > > what's comming back. > > It would not be hard to create an "auto explain analyze" mode that > implicitly runs EXPLAIN ANALYZE along with every query and logs the > result. On its face, it sounds like an obviously great idea. I just > don't see how you would put that to actual use, unless you want to read > server logs all day long. Grepping for query duration and using the > statistics views are much more manageable tuning methods. In my view > anyway. Well, the output would really need to go into some machine-readable format, since you certainly aren't going to read it. That would also make it trivial to identify plans that diverged greatly from reality.
> > Going back to the original discussion though, there's no reason this > > needs to involve EXPLAIN ANALYZE. All we want to know is what columns > > the planner is dealing with as a set rather than individually. > > This would log a whole bunch of column groups, since every moderately > interesting query uses a column in combination with some other column, > but you still won't know which ones you want the planner to optimize. Well, I guess there's actually two kinds of stats that are interesting... groups of columns that are often refered to as a group, ie: WHERE a='blah' and b='bleh' and c='blech' columns that are joined to other columns (perhaps in a group) > To get that piece of information, you'd need to do something like > principal component analysis over the column groups thus identified. > Which might be a fun thing to do. But for the moment I think it's > better to stick to declaring the interesting pairs/groups manually. Sure, but the idea is to make it easier to identify what those pairs might be. If the grouping info was alwas in a deterministic order, then simply doing SELECT columns, count(*) ... GROUP BY columns ORDER BY count(*) DESC LIMIT 10; would be very useful. And given the data, if someone wanted to do a more complex analysis they could. -- 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 5: don't forget to increase your free space map settings
