From: | Eric B(dot)Ridge <ebr(at)tcdi(dot)com> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | Mike Mascari <mascarm(at)mascari(dot)com>, NTPT <ntpt(at)centrum(dot)cz>, PostgreSQL-general <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Touch row ? |
Date: | 2004-01-24 19:53:58 |
Message-ID: | 0C926D42-4EA7-11D8-905E-000A95D98B3E@tcdi.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-announce pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Eric Ridge | 2004-01-24 20:45:47 | Re: Touch row ? |
Previous Message | Tom Lane | 2004-01-24 19:34:58 | Re: Touch row ? |
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2004-01-24 19:55:44 | Re: pl/pgsql docs 37.4.3. Row Types -- how do I use this function? |
Previous Message | Baldur Norddahl | 2004-01-24 19:46:15 | on cascade set null works on not null columns |