Re: Slow table update

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
>