Re: Touch row ?

On Jan 24, 2004, at 12:18 PM, Tom Lane wrote:
> This surprises me.  There's a moderate amount of overhead involved in
> a plpgsql trigger, but I'd not have thought it would swamp the added
> inefficiencies involved in a rule.  Notice that you're getting a double
> indexscan in the rule case --- that takes more time to plan, and more
> time to execute (observe the nearly double actual time for the top
> level
> plan node).
>
> What were you averaging here --- just the "total runtime" reported by
> EXPLAIN ANALYZE?

yes.

> It would be interesting to factor in the planning time
> too.  Could you retry this and measure the total elapsed time?  (psql's
> \timing command will help.)

\timing is cool!  never knew about it until just now.

test=# \timing
Timing is on.
test=# update foo_view set id = 1 where id = 1;
For 10 executions, the average is about 1.487ms

test=# update foo2 set id = 1 where id = 1;
For 10 executions, the average is about 1.420ms

so yeah, yer right, the view/rule is a bit slower.

I'm going to start using \timing for here on out...

<short pause>

Okay, so now I created two prepared statements:
prepare foo_view_statement (int, int) as update foo_view set id=$1
where id = $2;
prepare foo2_statement (int, int) as update foo2 set id=$1 where id =
$2;

execute foo_view_statement(1, 1);
average timing: 1.137

execute foo2_statement(1, 1);
average timing: 1.359;

So it seems if the plan is already made, the update against the rule is
actually a tad faster.  I don't know if the difference in speed is
enough to convince one (myself included) to start using prepared
statements, but it's another data point.

But still, a real-world example might prove all of this wrong.

eric