Re: WIP: explain analyze with 'rows' but not timing

Hello

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
relative cheap.

Regards

Pavel

>
> regards
> Tomas
>
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>