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

Re: Slow table update - SOLVED!

From: Laszlo Nagy <gandalf(at)shopzeus(dot)com>
To: Gregory Williamson <Gregory(dot)Williamson(at)digitalglobe(dot)com>
Cc: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, pgsql-performance(at)postgresql(dot)org, Daniel Fekete <danieleff(at)gmail(dot)com>
Subject: Re: Slow table update - SOLVED!
Date: 2008-12-29 13:11:48
Message-ID: 4958CC94.6020103@shopzeus.com (view raw or flat)
Thread:
Lists: pgsql-performance
>
> Inf 8.3 the HOT feature may help if the columns being updated are 
> indexed ... what version of PostgreSQL is this again ? (Forgive my 
> lack of memory -- the last few days I've forgotten a lot, heh heh.)
>
8.3.5.

The colum that was being updated is part of one small index only.

>
> Any chances to reduce those to a bare minimum, perhaps using 
> conditional index strategies or even some form of replication, so the 
> primary uses indexes related to the updates and the mirror uses 
> indexes related to the read-only / reporting needs ? Perhaps some form 
> of staging table with no indexes to load, check data, etc. and then 
> insert.
>
> Any way to reduce those ? Check the usage via the system stats on 
> table/index use and try removing some and testing to see what makes a 
> difference.
>
We tried to remove all indexes on a test system and the update was 
speedy. We are going to try to reduce the row size also move static 
description/name/textual data into a separate table, and leave 
frequently updated data in the original one. We tested this theoretical 
version:

Query returned successfully: 182752 rows affected, 56885 ms execution time.

This is much faster. However, this table is used by hundreds of 
programs. Anyway, I got the answer to my question.

Thank you!

   Laszlo


In response to

pgsql-performance by date

Next:From: Tom LaneDate: 2008-12-29 15:26:33
Subject: Re: rebellious pg stats collector (reopened case)
Previous:From: Dimitri FontaineDate: 2008-12-29 12:48:37
Subject: Re: Troubles dumping a very large table.

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