Skip site navigation (1) Skip section navigation (2)

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

From: Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
To: Tomas Vondra <tv(at)fuzzy(dot)cz>
Cc: pgsql-hackers(at)postgresql(dot)org
Subject: Re: WIP: explain analyze with 'rows' but not timing
Date: 2011-12-23 06:07:19
Message-ID: CAFj8pRBGRFfmUBiE69bU4c_k_1+ECifeKy=mW8A5jpTqi_O6vA@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-hackers
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
>

In response to

pgsql-hackers by date

Next:From: Kyotaro HORIGUCHIDate: 2011-12-23 07:38:28
Subject: Re: Allow substitute allocators for PGresult.
Previous:From: Nikhil SontakkeDate: 2011-12-23 04:02:10
Subject: Re: Review: Non-inheritable check constraints

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group