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

WIP: explain analyze with 'rows' but not timing

From: Tomas Vondra <tv(at)fuzzy(dot)cz>
To: pgsql-hackers(at)postgreSQL(dot)org
Subject: WIP: explain analyze with 'rows' but not timing
Date: 2011-12-23 00:37:43
Message-ID: 4EF3CD57.8060104@fuzzy.cz (view raw or flat)
Thread:
Lists: pgsql-hackers
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.

regards
Tomas

Attachment: explain-analyze-rows-only.diff
Description: text/plain (7.4 KB)

Responses

pgsql-hackers by date

Next:From: Nikhil SontakkeDate: 2011-12-23 03:25:26
Subject: Re: Review: Non-inheritable check constraints
Previous:From: Phil SorberDate: 2011-12-23 00:01:21
Subject: Re: WIP patch: Improve relation size functions such as pg_relation_size() to avoid producing an error when called against a no longer visible relation

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