2011/12/23 Tomas Vondra <tv(at)fuzzy(dot)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
> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
> To make changes to your subscription:
In response to
pgsql-hackers by date
|Next:||From: Kyotaro HORIGUCHI||Date: 2011-12-23 07:38:28|
|Subject: Re: Allow substitute allocators for PGresult.|
|Previous:||From: Nikhil Sontakke||Date: 2011-12-23 04:02:10|
|Subject: Re: Review: Non-inheritable check constraints|