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
