Re: EXPLAIN (plan off, rewrite off) for benchmarking

On Saturday, November 19, 2011 12:16:18 AM Tom Lane wrote:
> Andres Freund <andres@anarazel.de> writes:
> > Hi,
> > For benchmarking the parser I added the above options (dim suggested this
> > on irc) which proved to be rather useful for me.
> 
> What exactly is EXPLAIN printing, if you've not done planning? 
Nothing very interesting:

postgres=# EXPLAIN (rewrite off) SELECT 1, 'test', "pg_class"."oid",relname, 
relkind FROM pg_class WHERE oid = 1000;               QUERY PLAN                
------------------------------------------not rewriting query because auf !rewrite
(1 row)

Explain is just a vehicle here, I admit that. But on what else should I bolt 
it?
The only thing I could think of otherwise would be to do the parsing via from 
a C func. But to simmulate a real scenario there would require too much 
bootstrapping for my taste.

> Also, I
> believe the planner depends on the assumption that the rewriter has done
> its work, so these seem to amount to EXPLAIN (break_it).
"rewrite off" currently simply aborts before doing the rewriting and 
copyObject(). copyObject is the expensive part there for simple queries.

rewriting happened to be the functional part where I wanted to stop - because 
of the overhead of copyObject - instead of a goal in itself.

Btw, optimizing copyObject() memory usage wise would be another big 
performance gain. But even murkier than the stuff over in the lists thread...

> If you just want to benchmark parsing, perhaps CREATE RULE would be a
> useful environment.
I don't really see how one can use that to benchmark parsing. CREATE OR 
REPLACE VIEW is - not unexpectedly - far slower than EXPLAIN (rewrite off) or 
EXPLAIN (plan off).

for the statement:

SELECT * FROM pg_class WHERE oid = 1000;

rewrite off:
tps = 16086.694353
plan off, no copyObject()
tps = 15663.280093
plan off:
tps = 13471.272551
explain:
tps = 6162.161946
explain analyze:
tps = 5744.172839
normal execution:
tps = 6991.398740
CREATE OR REPLACE VIEW (after changing the log level):
tps = 2550.246625


Greetings,

Andres