pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedRe: [PERFORM] 答复: [PERFORM] Postgresql update op is very very slow
2+ messages / 2 participants
[nested] [flat]
* Re: [PERFORM] 答复: [PERFORM] Postgresql update op is very very slow
@ 2008-06-26 10:31 ` Pavan Deolasee <pavan.deolasee@gmail.com>
1 sibling, 0 replies; 2+ messages in thread
From: Pavan Deolasee @ 2008-06-26 10:31 UTC (permalink / raw)
To: jay <jackem.mojx@alibaba-inc.com>; +Cc: Heikki Linnakangas <heikki@enterprisedb.com>; pgsql-performance
2008/6/26 jay <jackem.mojx@alibaba-inc.com>:
> If we can do commit very 1000 row per round, it may resolve the
> problem.
> But PG not support transaction within function yet?
>
Yeah, transaction control is not supported inside functions. There are
some hacks using dblink to do transactions inside functions. You may
want to check that out.
I had suggested another hack in the past for very simplistic updates,
when you are sure that the tuple length does not change between
updates and you are ready to handle half updated table if there is a
crash or failure in between. May be for your case, where you are
updating a single column of the entire table and setting it to some
default value for all the rows, it may work fine. But please be aware
of data consistency issues before you try that. And it must be once in
a lifetime kind of hack.
http://postgresql-in.blogspot.com/2008/04/postgresql-in-place-update.html
Thanks,
Pavan
--
Pavan Deolasee
EnterpriseDB http://www.enterprisedb.com
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: [PERFORM] 答复: [PERFORM] Postgresql update op is very very slow
@ 2008-06-26 16:02 ` Scott Marlowe <scott.marlowe@gmail.com>
1 sibling, 0 replies; 2+ messages in thread
From: Scott Marlowe @ 2008-06-26 16:02 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: jay <jackem.mojx@alibaba-inc.com>; Heikki Linnakangas <heikki@enterprisedb.com>; pgsql-performance
2008/6/26 Tom Lane <tgl@sss.pgh.pa.us>:
> "jay" <jackem.mojx@alibaba-inc.com> writes:
>> I know the problem, because there are about 35 million rows , which
>> cost about 12G disk space and checkpoint segments use 64, but update
>> operation is in one transaction which lead fast fill up the checkpoint
>> segments and lead do checkpoints frequently, but checkpoints will cost lots
>> resources, so update operation become slowly and slowly and bgwrite won't
>> write because it's not commit yet.
>> Create a new table maybe a quick solution, but it's not appropriated in some
>> cases.
>> If we can do commit very 1000 row per round, it may resolve the
>> problem.
>
> No, that's utterly unrelated. Transaction boundaries have nothing to do
> with checkpoints.
True. But if you update 10000 rows and vacuum you can keep the bloat
to something reasonable.
On another note, I haven't seen anyone suggest adding the appropriate
where clause to keep from updating rows that already match. Cheap
compared to updating the whole table even if a large chunk aren't a
match. i.e.
... set col=0 where col <>0;
That should be the first thing you reach for in this situation, if it can help.
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2008-06-26 16:02 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2008-06-26 10:31 ` Pavan Deolasee <pavan.deolasee@gmail.com>
2008-06-26 16:02 ` Scott Marlowe <scott.marlowe@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox