Performance decrease

"Radovan Antloga" <radovan.antloga@siol.net> writes:

My test table has 15830 records with 190 fields.

190 fields in a table seems like rather a lot ... is that actually
representative of your intended applications?

I do like this:

update table
set field = null

Again, is that representative of something you'll be doing a lot in
practice? Most apps don't often update every row of a table, in my
experience.

After first execute I get time 3 seconds. Then I repeat
this update. After each update time increase. I get
4 sec, 7 sec, 10 sec, 12 sec, 15 sec, 18 sec, 21 sec.

There should be some increase because of the addition of dead rows,
but both the original 3 seconds and the rate of increase seem awfully
high for such a small table. What are you running this on?

For comparison purposes, here's what I see on a full-table UPDATE
of a 10000-row table on a rather slow HP box:

regression=# \timing
Timing is on.
regression=# create table t1 as select * from tenk1;
SELECT
Time: 1274.213 ms
regression=# update t1 set unique2 = null;
UPDATE 10000
Time: 565.664 ms
regression=# update t1 set unique2 = null;
UPDATE 10000
Time: 589.839 ms
regression=# update t1 set unique2 = null;
UPDATE 10000
Time: 593.735 ms
regression=# update t1 set unique2 = null;
UPDATE 10000
Time: 615.575 ms
regression=# update t1 set unique2 = null;
UPDATE 10000
Time: 755.456 ms
regression=#

Vacuuming brings the time back down:

regression=# vacuum t1;
VACUUM
Time: 242.406 ms
regression=# update t1 set unique2 = null;
UPDATE 10000
Time: 458.028 ms
regression=#

regards, tom lane

190 fields in a table seems like rather a lot ... is that actually
representative of your intended applications?

Test table is like table I use in production
with Firebird and Oracle db. Table has a lot of smallint
and integer fields. As you can see I have Firebird for
low cost projects (small companies) and Oracle medium
or large project.

Again, is that representative of something you'll be doing a lot in
practice? Most apps don't often update every row of a table, in my
experience.

I agree with you !
I have once or twice a month update on many records (~6000) but
not so many. I did not expect PG would have problems with
updating 15800 records.

My test was on Windows XP SP2.
I have AMD 64 2.1 GHz cpu with
1GB ram.

Regards,
Radovan Antloga

On Thu, Apr 20, 2006 at 06:10:21PM +0200, Radovan Antloga wrote:

I have once or twice a month update on many records (~6000) but
not so many. I did not expect PG would have problems with
updating 15800 records.

And generally speaking, it doesn't. But you do need to ensure that
you're vacuuming the database frequently enough. Autovacuum is a good
way to do that.

My test was on Windows XP SP2.
I have AMD 64 2.1 GHz cpu with
1GB ram.

One think to keep in mind is that the windows code is rather new, so it
is possible to find some performance issues there.
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461

On 20.04.2006, at 18:10 Uhr, Radovan Antloga wrote:

I have once or twice a month update on many records (~6000) but
not so many. I did not expect PG would have problems with
updating 15800 records.

It has no problems with that. We have a database where we often
update/insert rows with about one hundred columns. No problem so far.
Performance is in the sub 10ms range. The whole table has about
100000 records.

Do you wrap every update in a separate transaction? I do commits
every 200 updates for bulk updates.

cug

--
PharmaLine, Essen, GERMANY
Software and Database Development

"Radovan Antloga" <radovan.antloga@siol.net> writes:

190 fields in a table seems like rather a lot ... is that actually
representative of your intended applications?

Test table is like table I use in production
with Firebird and Oracle db. Table has a lot of smallint
and integer fields.

I did some experiments with CVS tip on updating all rows of a table with
lots of columns --- to be specific,

create table widetable(
int1 int, text1 text, num1 numeric,
int2 int, text2 text, num2 numeric,
int3 int, text3 text, num3 numeric,
...
int59 int, text59 text, num59 numeric,
int60 int, text60 text, num60 numeric
);

for 180 columns altogether, with 16k rows of data and the test query

update widetable set int30 = null;

The gprof profile looks like this:

% cumulative self self total
time seconds seconds calls ms/call ms/call name
19.77 1.22 1.22 _mcount
14.91 2.14 0.92 16385 0.06 0.06 XLogInsert
9.08 2.70 0.56 2932736 0.00 0.00 slot_deform_tuple
7.94 3.19 0.49 2965504 0.00 0.00 slot_getattr
6.48 3.59 0.40 2949120 0.00 0.00 ExecEvalVar
5.83 3.95 0.36 16384 0.02 0.02 ExecTargetList
4.70 4.24 0.29 16384 0.02 0.02 heap_fill_tuple
3.57 4.46 0.22 ExecEvalVar
2.43 4.61 0.15 _write_sys
2.27 4.75 0.14 16384 0.01 0.01 heap_compute_data_size
1.62 4.85 0.10 noshlibs
1.46 4.94 0.09 16384 0.01 0.03 heap_form_tuple
1.30 5.02 0.08 16384 0.00 0.01 ExecGetJunkAttribute
1.30 5.10 0.08 encore
1.13 5.17 0.07 16384 0.00 0.00 ExecFilterJunk
1.13 5.24 0.07 chunk2

The large number of calls to slot_deform_tuple() is annoying --- ideally
there'd be only one per row. But what actually happens is that the
ExecVariableList() optimization is disabled by the presence of one
non-user attribute in the scan's targetlist (ie, ctid, which is needed
by the top-level executor to do the UPDATE), not to mention that the
attribute(s) being updated will have non-Var expressions anyway. So we
execute the target list the naive way, and because the Vars referencing
the not-updated columns appear sequentially in the tlist, that means
each ExecEvalVar/slot_getattr ends up calling slot_deform_tuple again to
decipher just one more column of the tuple.

This is just an O(N) penalty, not O(N^2), but still it's pretty annoying
considering that all the infrastructure is there to do better. If we
were to determine the max attribute number to be fetched and call
slot_getsomeattrs() up front (as happens in the ExecVariableList case)
then we could save a significant constant factor --- perhaps as much as
10% of the runtime in this case.

The trick with such "optimizations" is to not turn them into
pessimizations --- if we decode attributes that end up not getting
fetched then we aren't saving cycles. So I'm thinking of tying this
specifically to the scan node's targetlist and only doing the
slot_getsomeattrs() call when we have decided to evaluate the
targetlist. Any columns referenced as part of the node's qual
conditions wouldn't participate in the improvement. We could
alternatively do the slot_getsomeattrs() call before evaluating the
quals, but I'm worried that this would be a loss in the case where
the qual condition fails and so the targetlist is never evaluated.

Comments, better ideas?

regards, tom lane