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

On Saturday, November 19, 2011 12:16:18 AM Tom Lane wrote:
> Andres Freund <andres(at)anarazel(dot)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