Skip site navigation (1) Skip section navigation (2)

Re: Slow table update

From: "Nikolas Everett" <nik9000(at)gmail(dot)com>
To: "Laszlo Nagy" <gandalf(at)shopzeus(dot)com>
Cc: "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>, "Gregory Williamson" <Gregory(dot)Williamson(at)digitalglobe(dot)com>, "pgsql-performance(at)postgresql(dot)org" <pgsql-performance(at)postgresql(dot)org>, "Daniel Fekete" <danieleff(at)gmail(dot)com>
Subject: Re: Slow table update
Date: 2008-12-26 14:34:09
Message-ID: d4e11e980812260634q433f036dt21509ea9c1ab1f8@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
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(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Laszlo Nagy <gandalf(at)shopzeus(dot)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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>

In response to

pgsql-performance by date

Next:From: Ted AllenDate: 2008-12-26 16:02:55
Subject: Re: Troubles dumping a very large table.
Previous:From: Dmitry KoterovDate: 2008-12-25 23:08:16
Subject: Bgwriter and pg_stat_bgwriter.buffers_clean aspects

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group