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

New EXPLAIN ANALYZE statement

From: Martijn van Oosterhout <kleptog(at)svana(dot)org>
To: pgsql-patches(at)postgresql(dot)org
Subject: New EXPLAIN ANALYZE statement
Date: 2001-07-20 14:49:12
Message-ID: 20010721004912.D6150@svana.org (view raw or flat)
Thread:
Lists: pgsql-patches
Well, I don't know how many people will have seen this one already so I'm
posting it here. This patch probably needs more feedback before being
applied.

- What it does:
Adds a new EXPLAIN ANALYZE statement which displays the plan tree, just like
EXPLAIN, but also executes the query and collects statistics on what
actually happened. The current statistics collected are:

Time till first tuple is returned
Time till all tuples are returned
Total number of tuples returned
Number of times Rescan is called

I wrote it to help analyze why certain queries were taking much longer than
the planner expected. I think for debugging purposes it is useful because by
asking people to post the output of EXPLAIN ANALYZE you can immediately see
the actual statistics and the planner's estimates next to each other.

- How it does it:
It adds a few calls in a few strategic places in the executor. Each node in
the query tree is accounted for separately. Only time processing the node is
counted, not the time before and after. It also adds two new files.

- Remaining issues:
Currently only select statements are allowed. This is because I can't work
out how to supress the actual effect of an UPDATE or DELETE query. I'm also
assuming that SELECT queries can't be rewritten into something that actually
changes something. Ouch! I just realised that functions can be called which
could do anything, so I truly need something to suppress the results.

Documentation, but I'll only do that if people are interested.

Do people like the name? The other thing I was thinking of was just ANALYZE
[query] but I thought that might be to much overloading of that name.

Anything else you people come up with.

- Example
# explain analyse select sum(cost) from dailycalls where band = 1 and billid is null;
NOTICE:  QUERY PLAN:

Aggregate  (cost=53723.88..53723.88 rows=1 width=8) (actual time=24956.02..24956.02 rows=1 loops=1)
  ->  Seq Scan on dailycalls  (cost=0.00..53708.34 rows=6216 width=8) (actual time=463.16..24941.63 rows=5418 loops=1)

EXPLAIN

- Where to get it
It's attached, but also available at:
http://svana.org/kleptog/pgsql/timing-explain.patch

Please CC any replies, as I'm subscribed nopost.
-- 
Martijn van Oosterhout <kleptog(at)svana(dot)org>
http://svana.org/kleptog/
> It would be nice if someone came up with a certification system that
> actually separated those who can barely regurgitate what they crammed over
> the last few weeks from those who command secret ninja networking powers.

Responses

pgsql-patches by date

Next:From: Bruce MomjianDate: 2001-07-20 15:40:12
Subject: Re: [PATCHES] Re: [PATCH] Cleanup of JDBC character encoding
Previous:From: Mark VolpeDate: 2001-07-19 17:13:44
Subject: Re: [PATCHES] Re: [PATCH] Re: Setuid functions

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