Hello 2011/12/23 Tomas Vondra <t...@fuzzy.cz>: > Hi all, > > most of the time I use auto_explain, all I need is duration of the query > and the plan with estimates and actual row counts. And it would be handy > to be able to catch long running queries with estimates that are > significantly off (say 100x lower or higher compared to actual row numbers). > > The gettimeofday() calls are not exactly cheap in some cases, so why to > pay that price when all you need is the number of rows? > > The patch attached does this: > > 1) adds INSTRUMENT_ROWS, a new InstrumentOption > > - counts rows without timing (no gettimeofday() callse) > - if you want timing info, use INSTRUMENT_TIMER > > 2) adds new option "TIMING" to EXPLAIN, i.e. > > EXPLAIN (ANALYZE ON, TIMING ON) SELECT ... > > 3) adds auto_explain.log_rows_only (false by default) > > - if you set this to 'true', then the instrumentation will just > count rows, without calling gettimeofday() > > > It works quite well, except one tiny issue - when the log_rows_only is > set to false (so that auto_explain requires timing), it silently > overrides the EXPLAIN option. So that even when the user explicitly > disables timing (TIMING OFF), it's overwritten and the explain collects > the timing data. > > I could probably hide the timing info, but that'd make the issue even > worse (the user would not notice that the timing was actually enabled). > > Maybe the right thing would be to explicitly disable timing for queries > executed with "EXPLAIN (TIMING OFF)". Any other ideas how to make this > work reasonably? > > The patch does not implement any checks (how far is the estimate from > the reality) yet, that'll be round two.
It is interesting idea - but maybe we can have a have a different metric than time - this is very unstable quantity - mainly on production overloaded servers. It is good idea - we need a tool for bad statistic searching that is relative cheap. Regards Pavel > > regards > Tomas > > > -- > Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-hackers > -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers