Received: from localhost (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id 8181164FC45 for ; Fri, 26 Dec 2008 10:34:14 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 72445-01 for ; Fri, 26 Dec 2008 10:34:11 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from wa-out-1112.google.com (wa-out-1112.google.com [209.85.146.183]) by mail.postgresql.org (Postfix) with ESMTP id 68F5664FC4A for ; Fri, 26 Dec 2008 10:34:11 -0400 (AST) Received: by wa-out-1112.google.com with SMTP id j4so2186685wah.19 for ; Fri, 26 Dec 2008 06:34:09 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:received:received:message-id:date:from:to :subject:cc:in-reply-to:mime-version:content-type:references; bh=lRnq8wq9TE88Ho0g2FtZOGI5yW06Mru2TczoNFFBCv8=; b=lyRlnMaSFbvQhzS4KXD0j4cSIWug72XvYwsdF6JrhEuy+deR1/+EC9msX1iMbmuYEo r2hDuZx8fhw3e/XsHg+U9YYuodxGJQwX3ryQRokbrWJ/uJxLh0aGSmcZj1zzLzBarzJI lv965qHM8QZ3gON3GBsw4dwi1wTw4bMj9I1As= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=message-id:date:from:to:subject:cc:in-reply-to:mime-version :content-type:references; b=Yb45sAKlgWqCZc4eRQ2Teswg32Ibpg/8GfJpISSESlmXiB4ZSuRkJffSvFjT7kMz1k o28JicZGTUdO3LdlIM420BQcZ8py0LlYVLUXoFT5Mk+sxkqz1oMvqtDKhdCgx9MVICBy lyJlsAQH4gRlu4JSxlju6xIBgpfg/CHqgSXV4= Received: by 10.114.131.1 with SMTP id e1mr6899104wad.222.1230302049780; Fri, 26 Dec 2008 06:34:09 -0800 (PST) Received: by 10.115.47.6 with HTTP; Fri, 26 Dec 2008 06:34:09 -0800 (PST) Message-ID: Date: Fri, 26 Dec 2008 09:34:09 -0500 From: "Nikolas Everett" To: "Laszlo Nagy" Subject: Re: Slow table update Cc: "Tom Lane" , "Gregory Williamson" , "pgsql-performance@postgresql.org" , "Daniel Fekete" In-Reply-To: <7032.1229952622@sss.pgh.pa.us> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_81522_7444238.1230302049772" References: <494F749A.5090603@shopzeus.com> <494F7B48.6070204@shopzeus.com> <8B319E5A30FF4A48BE7EEAAF609DB233021F389A@COMAIL01.digitalglobe.com> <494F8DFC.1000001@shopzeus.com> <7032.1229952622@sss.pgh.pa.us> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.001 tagged_above=0 required=5 tests=HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 200812/213 X-Sequence-Number: 32249 ------=_Part_81522_7444238.1230302049772 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline 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 wrote: > Laszlo Nagy 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 > ------=_Part_81522_7444238.1230302049772 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline 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

------=_Part_81522_7444238.1230302049772--