pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Rob Nagler <nagler@bivio.biz>
To: pgsql-performance@postgresql.org
Subject: Re: vacuum locking
Date: Mon, 27 Oct 2003 09:24:47 -0700
Message-ID: <16285.18127.773000.825179@gargle.gargle.HOWL> (raw)
In-Reply-To: <87ekx2uqle.fsf@stark.dyndns.tv>
References: <16272.318.870000.93584@gargle.gargle.HOWL>
	<200310230814.56738.mweilguni@sime.com>
	<29123.1066915615@sss.pgh.pa.us>
	<200310240817.22586.mweilguni@sime.com>
	<16281.41629.69000.492473@gargle.gargle.HOWL>
	<87ekx2uqle.fsf@stark.dyndns.tv>

Greg Stark writes:
> I don't understand why you would expect overwriting to win here. 
> What types of updates do you do on these tables? 

These are statistics that we're adjusting.  I think that's pretty
normal stuff.  The DSS component is the avg() of these numbers on
particular groups.  The groups are related to foreign keys to
customers and other things.

> Normally I found using update on such a table was too awkward to
> contemplate so I just delete all the relation records that I'm
> replacing for the key I'm working with and insert new ones. This
> always works out to be cleaner code. In fact I usually leave such
> tables with no UPDATE grants on them.

In accounting apps, we do this, too.  It's awkward with all the
relationships to update all the records in the right order.  But
Oracle wins on delete/insert, too, because it reuses the tuples it
already has in memory, and it can reuse the same foreign key index
pages, too, since the values are usually the same.

The difference between Oracle and postgres seems to be optimism.
postgres assumes the transaction will fail and/or that a transaction
will modify lots of data that is used by other queries going on in
parallel.  Oracle assumes that the transaction is going to be
committed, and it might as well make the changes in place.

> In that situation I would have actually expected Postgres to do as well as or
> better than Oracle since that makes them both functionally
> equivalent.

I'll find out soon enough. :-)

Rob





view thread (45+ messages)  latest in thread

Message-ID: <16285.18127.773000.825179@gargle.gargle.HOWL>
Permalink:  ../16285.18127.773000.825179@gargle.gargle.HOWL/
Also on:    postgresql.org/message-id/16285.18127.773000.825179@gargle.gargle.HOWL

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-performance@postgresql.org
  Cc: nagler@bivio.biz
  Subject: Re: vacuum locking
  In-Reply-To: <16285.18127.773000.825179@gargle.gargle.HOWL>
  References: <16272.318.870000.93584@gargle.gargle.HOWL> <200310230814.56738.mweilguni@sime.com> <29123.1066915615@sss.pgh.pa.us> <200310240817.22586.mweilguni@sime.com> <16281.41629.69000.492473@gargle.gargle.HOWL> <87ekx2uqle.fsf@stark.dyndns.tv> <16285.18127.773000.825179@gargle.gargle.HOWL>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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