pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Nikolas Everett <nik9000@gmail.com>
To: Laszlo Nagy <gandalf@shopzeus.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Gregory Williamson <Gregory.Williamson@digitalglobe.com>
Cc: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Cc: Daniel Fekete <danieleff@gmail.com>
Subject: Re: Slow table update
Date: Fri, 26 Dec 2008 09:34:09 -0500
Message-ID: <d4e11e980812260634q433f036dt21509ea9c1ab1f8@mail.gmail.com> (raw)
In-Reply-To: <7032.1229952622@sss.pgh.pa.us>
References: <494F749A.5090603@shopzeus.com>
	<494F7B48.6070204@shopzeus.com>
	<8B319E5A30FF4A48BE7EEAAF609DB233021F389A@COMAIL01.digitalglobe.com>
	<494F8DFC.1000001@shopzeus.com>
	<7032.1229952622@sss.pgh.pa.us>

The thing to keep in mind is that every update creates a new row version
that has to be indexed for all indexes on the table, not just the indexes on
the column updated.  You can test the weight of indexes by copying the table
then trying your query again.

I've heard tell that if you have a table that updates frequently but needs
to be indexed you can get some performance by breaking it into two tables
with the same primary key.  One table with the stuff you index and another
table with the stuff you update.

I hope this helps.


On Mon, Dec 22, 2008 at 8:30 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

> Laszlo Nagy <gandalf@shopzeus.com> writes:
> >> If the table has some sort of FK relations it might be being slowed by
> >> the need to check a row meant to be deleted has any children.
> >>
> > If you look at my SQL, there is only one column to be updated. That
> > column has no foreign key constraint.
>
> That was not the question that was asked.
>
> > My other idea was that there are so many indexes on this table, maybe
> > the update is slow because of the indexes?
>
> Updating indexes is certainly very far from being free.  How many is
> "many"?
>
>                        regards, tom lane
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>

view thread (9+ messages)  latest in thread

Message-ID: <d4e11e980812260634q433f036dt21509ea9c1ab1f8@mail.gmail.com>
Permalink:  ../d4e11e980812260634q433f036dt21509ea9c1ab1f8@mail.gmail.com/
Also on:    postgresql.org/message-id/d4e11e980812260634q433f036dt21509ea9c1ab1f8@mail.gmail.com

 · 

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: nik9000@gmail.com, gandalf@shopzeus.com, tgl@sss.pgh.pa.us, Gregory.Williamson@digitalglobe.com, danieleff@gmail.com
  Subject: Re: Slow table update
  In-Reply-To: <d4e11e980812260634q433f036dt21509ea9c1ab1f8@mail.gmail.com>
  References: <494F749A.5090603@shopzeus.com> <494F7B48.6070204@shopzeus.com> <8B319E5A30FF4A48BE7EEAAF609DB233021F389A@COMAIL01.digitalglobe.com> <494F8DFC.1000001@shopzeus.com> <7032.1229952622@sss.pgh.pa.us> <d4e11e980812260634q433f036dt21509ea9c1ab1f8@mail.gmail.com>

* 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