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
The patch does not implement any checks (how far is the estimate from
the reality) yet, that'll be round two.
pgsql-hackers by date
|Next:||From: Nikhil Sontakke||Date: 2011-12-23 03:25:26|
|Subject: Re: Review: Non-inheritable check constraints|
|Previous:||From: Phil Sorber||Date: 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|