Обсуждение: Terrible Write Performance of a Stored Procedure
Hello, all. I'm finding that write performance of a certain stored procedure is abysmal. I need to be able to sustain approximately 20 calls to this procedure per second, but am finding that, on the average, each call takes 2 seconds in itself, in addition to pegging a single processor at 100% for the duration of the call. Additionally, while the stored procedure calls are being made a single worker does a full-table scan once every half-hours. Being a software developer more than a DBA I hope those on this list will be kind enough to help me troubleshoot and correct this issue. I do not know what information would be exactly pertinent, but I have included table definitions, configurations and the function in question below. I am using PostgreSQL 8.3 on a Linux Intel Core Duo system with 2GB of RAM and am running Postgres on XFS. Here are the relevant settings of my postgresql.conf: max_connections = 25 shared_buffers = 512MB max_fsm_pages = 153600 fsync = off synchronous_commit = off wal_writer_delay = 10000ms commit_delay = 100000 commit_siblings = 100 checkpoint_segments = 64 checkpoint_completion_target = 0.9 effective_cache_size = 1024MB track_activities = on track_counts = on update_process_title = on autovacuum = on log_autovacuum_min_duration = 1000 autovacuum_vacuum_threshold = 50 autovacuum_analyze_threshold = 50 Here is the relevant table definition: DROP TABLE IF EXISTS amazon_items CASCADE; CREATE TABLE amazon_items ( asin char(10) PRIMARY KEY, locale varchar(10) NOT NULL DEFAULT 'US', currency_code char(3) DEFAULT 'USD', isbn char(13), sales_rank integer, offers text, offer_pages integer DEFAULT 10, offers_last_updated timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, UNIQUE (asin, locale) ); The stored procedure in question, plus supporting procedures: CREATE OR REPLACE FUNCTION item_data_insert( iasin TEXT, iauthor TEXT, ibinding TEXT, icurrency_code TEXT, iisbn TEXT, iheight INTEGER, iwidth INTEGER, ilength INTEGER, iweight INTEGER, ilist_price INTEGER, iproduct_group TEXT, isales_rank INTEGER, ititle TEXT, ioffer_pages INTEGER, ioffers TEXT) RETURNS VOID AS $$ DECLARE y integer[]; BEGIN y[1] := iwidth; y[2] := ilength; y[3] := iheight; y[4] := iweight; BEGIN INSERT INTO item_details (isbn, title, author, binding, list_price, dimensions) VALUES (iisbn, ititle, iauthor, ibinding, ilist_price, y); EXCEPTION WHEN unique_violation THEN UPDATE item_details SET title = ititle, author = iauthor, binding = ibinding, list_price = ilist_price, dimensions = y WHERE isbn = iisbn; END; BEGIN INSERT INTO amazon_items (asin, sales_rank, offers, offer_pages, isbn) VALUES (iasin, isales_rank, crunch(ioffers), ioffer_pages, iisbn); EXCEPTION WHEN unique_violation THEN IF isales_rank IS NOT NULL THEN UPDATE amazon_items SET sales_rank = isales_rank WHERE asin = iasin; END IF; IF ioffers IS NOT NULL THEN UPDATE amazon_items SET offers = crunch(ioffers), offers_last_updated = CURRENT_TIMESTAMP, offer_pages = ioffer_pages WHERE asin = iasin; END IF; END; END; $$ LANGUAGE plpgsql; CREATE OR REPLACE FUNCTION crunch(text) RETURNS text AS $$ BEGIN RETURN encode(text2bytea($1), 'base64'); END; $$ LANGUAGE 'plpgsql' IMMUTABLE STRICT; CREATE OR REPLACE FUNCTION text2bytea(text) RETURNS bytea AS $$ BEGIN RETURN $1; END; $$ LANGUAGE 'plpgsql' IMMUTABLE STRICT; Thanks, Brian
On Friday 26 June 2009, Brian Troutwine <goofyheadedpunk@gmail.com> wrote: > CREATE TABLE amazon_items ( > asin char(10) PRIMARY KEY, > locale varchar(10) NOT NULL DEFAULT 'US', > currency_code char(3) DEFAULT 'USD', > isbn char(13), > sales_rank integer, > offers text, > offer_pages integer DEFAULT 10, > offers_last_updated timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, > UNIQUE (asin, locale) > ); > Indexes are good things. Try them. Particularly on the isbn field. -- Overshoot = http://www.theoildrum.com/files/evoltuion_timeline.JPG
On Fri, Jun 26, 2009 at 3:30 PM, Brian Troutwine<goofyheadedpunk@gmail.com> wrote: > Hello, all. > > I'm finding that write performance of a certain stored procedure is > abysmal. I need to be able to sustain approximately 20 calls to this > procedure per second, but am finding that, on the average, each call > takes 2 seconds in itself, in addition to pegging a single processor > at 100% for the duration of the call. Additionally, while the stored > procedure calls are being made a single worker does a full-table scan > once every half-hours. > > Being a software developer more than a DBA I hope those on this list > will be kind enough to help me troubleshoot and correct this issue. I > do not know what information would be exactly pertinent, but I have > included table definitions, configurations and the function in > question below. I am using PostgreSQL 8.3 on a Linux Intel Core Duo > system with 2GB of RAM and am running Postgres on XFS. Here are the > relevant settings of my postgresql.conf: > > max_connections = 25 > shared_buffers = 512MB > max_fsm_pages = 153600 > fsync = off > synchronous_commit = off > wal_writer_delay = 10000ms > commit_delay = 100000 > commit_siblings = 100 > checkpoint_segments = 64 > checkpoint_completion_target = 0.9 > effective_cache_size = 1024MB > track_activities = on > track_counts = on > update_process_title = on > autovacuum = on > log_autovacuum_min_duration = 1000 > autovacuum_vacuum_threshold = 50 > autovacuum_analyze_threshold = 50 > > Here is the relevant table definition: > > DROP TABLE IF EXISTS amazon_items CASCADE; > CREATE TABLE amazon_items ( > asin char(10) PRIMARY KEY, > locale varchar(10) NOT NULL DEFAULT 'US', > currency_code char(3) DEFAULT 'USD', > isbn char(13), > sales_rank integer, > offers text, > offer_pages integer DEFAULT 10, > offers_last_updated timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, > UNIQUE (asin, locale) > ); > > The stored procedure in question, plus supporting procedures: > > CREATE OR REPLACE FUNCTION item_data_insert( > iasin TEXT, iauthor TEXT, ibinding TEXT, icurrency_code TEXT, > iisbn TEXT, iheight INTEGER, iwidth INTEGER, ilength INTEGER, > iweight INTEGER, > ilist_price INTEGER, iproduct_group TEXT, isales_rank INTEGER, > ititle TEXT, ioffer_pages INTEGER, ioffers TEXT) > RETURNS VOID AS > $$ > DECLARE > y integer[]; > BEGIN > y[1] := iwidth; > y[2] := ilength; > y[3] := iheight; > y[4] := iweight; > BEGIN > INSERT INTO item_details > (isbn, title, author, binding, list_price, dimensions) > VALUES > (iisbn, ititle, iauthor, ibinding, ilist_price, y); > EXCEPTION WHEN unique_violation THEN > UPDATE item_details SET > title = ititle, > author = iauthor, > binding = ibinding, > list_price = ilist_price, > dimensions = y > WHERE isbn = iisbn; > END; > BEGIN > INSERT INTO amazon_items > (asin, sales_rank, offers, offer_pages, isbn) > VALUES > (iasin, isales_rank, crunch(ioffers), ioffer_pages, iisbn); > EXCEPTION WHEN unique_violation THEN > IF isales_rank IS NOT NULL THEN > UPDATE amazon_items SET > sales_rank = isales_rank > WHERE asin = iasin; > END IF; > IF ioffers IS NOT NULL THEN > UPDATE amazon_items SET > offers = crunch(ioffers), > offers_last_updated = CURRENT_TIMESTAMP, > offer_pages = ioffer_pages > WHERE asin = iasin; > END IF; > END; > END; > $$ > LANGUAGE plpgsql; > > CREATE OR REPLACE FUNCTION crunch(text) > RETURNS text AS > $$ > BEGIN > RETURN encode(text2bytea($1), 'base64'); > END; > $$ > LANGUAGE 'plpgsql' IMMUTABLE STRICT; > > CREATE OR REPLACE FUNCTION text2bytea(text) > RETURNS bytea AS > $$ > BEGIN > RETURN $1; > END; > $$ > LANGUAGE 'plpgsql' IMMUTABLE STRICT; some general tips: *) use indexes to optimize where and join conditions. for example, update yadda set yadda where foo = bar, make sure that there is an index on foo. As alan noted this is almost definitely your problem. *) prefer '_' to 'i' to prefix arguments (more readable and less chance for error). *) use varchar, not char (always). merlin
> Indexes are good things. Try them. Particularly on the isbn field. I'm not sure why amazon_items.isbn should be given an index. item_details.isbn is used in a WHERE clause and is given an index accordingly, but not amazon_items.isbn. Brian On Fri, Jun 26, 2009 at 12:40 PM, Alan Hodgson<ahodgson@simkin.ca> wrote: > On Friday 26 June 2009, Brian Troutwine <goofyheadedpunk@gmail.com> wrote: >> CREATE TABLE amazon_items ( >> asin char(10) PRIMARY KEY, >> locale varchar(10) NOT NULL DEFAULT 'US', >> currency_code char(3) DEFAULT 'USD', >> isbn char(13), >> sales_rank integer, >> offers text, >> offer_pages integer DEFAULT 10, >> offers_last_updated timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, >> UNIQUE (asin, locale) >> ); >> > > Indexes are good things. Try them. Particularly on the isbn field. > > -- > Overshoot = http://www.theoildrum.com/files/evoltuion_timeline.JPG > > -- > Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-performance >
> Turn commit delay and commit siblings off. Why? Brian On Fri, Jun 26, 2009 at 1:06 PM, Scott Mead<scott.mead@enterprisedb.com> wrote: > -- sorry for the top-post and short response. > > Turn commit delay and commit siblings off. > > --Scott > > On 6/26/09, Brian Troutwine <goofyheadedpunk@gmail.com> wrote: >> Hello, all. >> >> I'm finding that write performance of a certain stored procedure is >> abysmal. I need to be able to sustain approximately 20 calls to this >> procedure per second, but am finding that, on the average, each call >> takes 2 seconds in itself, in addition to pegging a single processor >> at 100% for the duration of the call. Additionally, while the stored >> procedure calls are being made a single worker does a full-table scan >> once every half-hours. >> >> Being a software developer more than a DBA I hope those on this list >> will be kind enough to help me troubleshoot and correct this issue. I >> do not know what information would be exactly pertinent, but I have >> included table definitions, configurations and the function in >> question below. I am using PostgreSQL 8.3 on a Linux Intel Core Duo >> system with 2GB of RAM and am running Postgres on XFS. Here are the >> relevant settings of my postgresql.conf: >> >> max_connections = 25 >> shared_buffers = 512MB >> max_fsm_pages = 153600 >> fsync = off >> synchronous_commit = off >> wal_writer_delay = 10000ms >> commit_delay = 100000 >> commit_siblings = 100 >> checkpoint_segments = 64 >> checkpoint_completion_target = 0.9 >> effective_cache_size = 1024MB >> track_activities = on >> track_counts = on >> update_process_title = on >> autovacuum = on >> log_autovacuum_min_duration = 1000 >> autovacuum_vacuum_threshold = 50 >> autovacuum_analyze_threshold = 50 >> >> Here is the relevant table definition: >> >> DROP TABLE IF EXISTS amazon_items CASCADE; >> CREATE TABLE amazon_items ( >> asin char(10) PRIMARY KEY, >> locale varchar(10) NOT NULL DEFAULT 'US', >> currency_code char(3) DEFAULT 'USD', >> isbn char(13), >> sales_rank integer, >> offers text, >> offer_pages integer DEFAULT 10, >> offers_last_updated timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, >> UNIQUE (asin, locale) >> ); >> >> The stored procedure in question, plus supporting procedures: >> >> CREATE OR REPLACE FUNCTION item_data_insert( >> iasin TEXT, iauthor TEXT, ibinding TEXT, icurrency_code TEXT, >> iisbn TEXT, iheight INTEGER, iwidth INTEGER, ilength INTEGER, >> iweight INTEGER, >> ilist_price INTEGER, iproduct_group TEXT, isales_rank INTEGER, >> ititle TEXT, ioffer_pages INTEGER, ioffers TEXT) >> RETURNS VOID AS >> $$ >> DECLARE >> y integer[]; >> BEGIN >> y[1] := iwidth; >> y[2] := ilength; >> y[3] := iheight; >> y[4] := iweight; >> BEGIN >> INSERT INTO item_details >> (isbn, title, author, binding, list_price, dimensions) >> VALUES >> (iisbn, ititle, iauthor, ibinding, ilist_price, y); >> EXCEPTION WHEN unique_violation THEN >> UPDATE item_details SET >> title = ititle, >> author = iauthor, >> binding = ibinding, >> list_price = ilist_price, >> dimensions = y >> WHERE isbn = iisbn; >> END; >> BEGIN >> INSERT INTO amazon_items >> (asin, sales_rank, offers, offer_pages, isbn) >> VALUES >> (iasin, isales_rank, crunch(ioffers), ioffer_pages, iisbn); >> EXCEPTION WHEN unique_violation THEN >> IF isales_rank IS NOT NULL THEN >> UPDATE amazon_items SET >> sales_rank = isales_rank >> WHERE asin = iasin; >> END IF; >> IF ioffers IS NOT NULL THEN >> UPDATE amazon_items SET >> offers = crunch(ioffers), >> offers_last_updated = CURRENT_TIMESTAMP, >> offer_pages = ioffer_pages >> WHERE asin = iasin; >> END IF; >> END; >> END; >> $$ >> LANGUAGE plpgsql; >> >> CREATE OR REPLACE FUNCTION crunch(text) >> RETURNS text AS >> $$ >> BEGIN >> RETURN encode(text2bytea($1), 'base64'); >> END; >> $$ >> LANGUAGE 'plpgsql' IMMUTABLE STRICT; >> >> CREATE OR REPLACE FUNCTION text2bytea(text) >> RETURNS bytea AS >> $$ >> BEGIN >> RETURN $1; >> END; >> $$ >> LANGUAGE 'plpgsql' IMMUTABLE STRICT; >> >> Thanks, >> Brian >> >> -- >> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) >> To make changes to your subscription: >> http://www.postgresql.org/mailpref/pgsql-performance >> > > -- > Sent from my mobile device > > -- > Scott Mead > Sr. Systems Engineer > EnterpriseDB > > scott.mead@enterprisedb.com > C: 607 765 1395 > www.enterprisedb.com >
On Fri, Jun 26, 2009 at 4:36 PM, Brian Troutwine <goofyheadedpunk@gmail.com> wrote:
> Turn commit delay and commit siblings off.Why?
Sorry about the short and sweet, was driving:
Having those settings enabled basically does the following:
" Do not complete the I/O for a commit until you have either commit_siblings commits also ready, or you have waited .55 seconds."
Basically, if you make 1 commit, you will sit there waiting until either 99 other commits take place, or ~ 1/2 second goes by. This is really designed to alleviate the i/o involved with the commit process, and since you've turned fsync off anyway (which means when I commit, don't write to disk, just to memory), you're waiting around for 99 of your best buddies to come along for 1/2 second for basically... nothing.
I will note btw, that fsync=off is really only recommended when you aren't concerned about your data in the event of disk / power / general node failure. With fsync=off, your journal (REDO / xlog / WAL whatever you want to call it) is not consistent with the latest changes to your database, risking data loss in the event of failure.
Test it out, let me know how it goes.
--SCott
Brian
On Fri, Jun 26, 2009 at 1:06 PM, Scott Mead<scott.mead@enterprisedb.com> wrote:
> -- sorry for the top-post and short response.
>
> Turn commit delay and commit siblings off.
>
> --Scott
>
> On 6/26/09, Brian Troutwine <goofyheadedpunk@gmail.com> wrote:
>> Hello, all.
>>
>> I'm finding that write performance of a certain stored procedure is
>> abysmal. I need to be able to sustain approximately 20 calls to this
>> procedure per second, but am finding that, on the average, each call
>> takes 2 seconds in itself, in addition to pegging a single processor
>> at 100% for the duration of the call. Additionally, while the stored
>> procedure calls are being made a single worker does a full-table scan
>> once every half-hours.
>>
>> Being a software developer more than a DBA I hope those on this list
>> will be kind enough to help me troubleshoot and correct this issue. I
>> do not know what information would be exactly pertinent, but I have
>> included table definitions, configurations and the function in
>> question below. I am using PostgreSQL 8.3 on a Linux Intel Core Duo
>> system with 2GB of RAM and am running Postgres on XFS. Here are the
>> relevant settings of my postgresql.conf:
>>
>> max_connections = 25
>> shared_buffers = 512MB
>> max_fsm_pages = 153600
>> fsync = off
>> synchronous_commit = off
>> wal_writer_delay = 10000ms
>> commit_delay = 100000
>> commit_siblings = 100
>> checkpoint_segments = 64
>> checkpoint_completion_target = 0.9
>> effective_cache_size = 1024MB
>> track_activities = on
>> track_counts = on
>> update_process_title = on
>> autovacuum = on
>> log_autovacuum_min_duration = 1000
>> autovacuum_vacuum_threshold = 50
>> autovacuum_analyze_threshold = 50
>>
>> Here is the relevant table definition:
>>
>> DROP TABLE IF EXISTS amazon_items CASCADE;
>> CREATE TABLE amazon_items (
>> asin char(10) PRIMARY KEY,
>> locale varchar(10) NOT NULL DEFAULT 'US',
>> currency_code char(3) DEFAULT 'USD',
>> isbn char(13),
>> sales_rank integer,
>> offers text,
>> offer_pages integer DEFAULT 10,
>> offers_last_updated timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
>> UNIQUE (asin, locale)
>> );
>>
>> The stored procedure in question, plus supporting procedures:
>>
>> CREATE OR REPLACE FUNCTION item_data_insert(
>> iasin TEXT, iauthor TEXT, ibinding TEXT, icurrency_code TEXT,
>> iisbn TEXT, iheight INTEGER, iwidth INTEGER, ilength INTEGER,
>> iweight INTEGER,
>> ilist_price INTEGER, iproduct_group TEXT, isales_rank INTEGER,
>> ititle TEXT, ioffer_pages INTEGER, ioffers TEXT)
>> RETURNS VOID AS
>> $$
>> DECLARE
>> y integer[];
>> BEGIN
>> y[1] := iwidth;
>> y[2] := ilength;
>> y[3] := iheight;
>> y[4] := iweight;
>> BEGIN
>> INSERT INTO item_details
>> (isbn, title, author, binding, list_price, dimensions)
>> VALUES
>> (iisbn, ititle, iauthor, ibinding, ilist_price, y);
>> EXCEPTION WHEN unique_violation THEN
>> UPDATE item_details SET
>> title = ititle,
>> author = iauthor,
>> binding = ibinding,
>> list_price = ilist_price,
>> dimensions = y
>> WHERE isbn = iisbn;
>> END;
>> BEGIN
>> INSERT INTO amazon_items
>> (asin, sales_rank, offers, offer_pages, isbn)
>> VALUES
>> (iasin, isales_rank, crunch(ioffers), ioffer_pages, iisbn);
>> EXCEPTION WHEN unique_violation THEN
>> IF isales_rank IS NOT NULL THEN
>> UPDATE amazon_items SET
>> sales_rank = isales_rank
>> WHERE asin = iasin;
>> END IF;
>> IF ioffers IS NOT NULL THEN
>> UPDATE amazon_items SET
>> offers = crunch(ioffers),
>> offers_last_updated = CURRENT_TIMESTAMP,
>> offer_pages = ioffer_pages
>> WHERE asin = iasin;
>> END IF;
>> END;
>> END;
>> $$
>> LANGUAGE plpgsql;
>>
>> CREATE OR REPLACE FUNCTION crunch(text)
>> RETURNS text AS
>> $$
>> BEGIN
>> RETURN encode(text2bytea($1), 'base64');
>> END;
>> $$
>> LANGUAGE 'plpgsql' IMMUTABLE STRICT;
>>
>> CREATE OR REPLACE FUNCTION text2bytea(text)
>> RETURNS bytea AS
>> $$
>> BEGIN
>> RETURN $1;
>> END;
>> $$
>> LANGUAGE 'plpgsql' IMMUTABLE STRICT;
>>
>> Thanks,
>> Brian
>>
>> --
>> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-performance
>>
>
> --
> Sent from my mobile device
>
> --
> Scott Mead
> Sr. Systems Engineer
> EnterpriseDB
>
> scott.mead@enterprisedb.com
> C: 607 765 1395
> www.enterprisedb.com
>
On Fri, 26 Jun 2009, Scott Mead wrote: > Having those settings enabled basically does the following: > " Do not complete the I/O for a commit until you have either commit_siblings commits also ready, or you have waited.55 seconds." > > Basically, if you make 1 commit, you will sit there waiting until either 99 other commits take place, or ~ 1/2 secondgoes by. You're right that it should be removed, but this explanation is wrong. The behavior as configured is actually "if there are >=100 other transactions in progress, wait 0.1 second before committing after the first one gets committed", in hopes that one of the other 100 might also join along in the disk write. Since in this case max_connections it set to 100, it's actually impossible for the commit_delay/commit_siblings behavior to trigger give this configuration. That's one reason it should be removed. The other is that i general, if you don't exactly what you're doing, you shouldn't be touching either parameters; they don't do what people expect them to and it's extremely unlikely you'll encounter any of the rare use cases where they might help. I don't think any of the sync or write parameters have anything to do with this problem though, it seems like a problem with the referential bits taking too long to execute. -- * Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD
You're right that it should be removed, but this explanation is wrong. The behavior as configured is actually "if there are >=100 other transactions in progress, wait 0.1 second before committing after the first one gets committed", in hopes that one of the other 100 might also join along in the disk write.
Thanks for the correction. My question is how you're getting .1 seconds from his commit_delay?
if (CommitDelay > 0 && enableFsync &&
CountActiveBackends() >= CommitSiblings)
pg_usleep(CommitDelay);
Wouldn't this actually be 1 second based on a commit_delay of 100000?
Since in this case max_connections it set to 100, it's actually impossible for the commit_delay/commit_siblings behavior to trigger give this configuration. That's one reason it should be removed. The other is that i general, if you don't exactly what you're doing, you shouldn't be touching either parameters; they don't do what people expect them to and it's extremely unlikely you'll encounter any of the rare use cases where they might help.
After looking, I agree, thanks again for the correction Greg.
--Scott
On Jun 26, 9:30 pm, goofyheadedp...@gmail.com (Brian Troutwine) wrote: > Hello, all. > > CREATE OR REPLACE FUNCTION item_data_insert( > iasin TEXT, iauthor TEXT, ibinding TEXT, icurrency_code TEXT, > iisbn TEXT, iheight INTEGER, iwidth INTEGER, ilength INTEGER, > iweight INTEGER, > ilist_price INTEGER, iproduct_group TEXT, isales_rank INTEGER, > ititle TEXT, ioffer_pages INTEGER, ioffers TEXT) > RETURNS VOID AS > $$ > DECLARE > y integer[]; > BEGIN > y[1] := iwidth; > y[2] := ilength; > y[3] := iheight; > y[4] := iweight; > BEGIN > INSERT INTO item_details > (isbn, title, author, binding, list_price, dimensions) > VALUES > (iisbn, ititle, iauthor, ibinding, ilist_price, y); > EXCEPTION WHEN unique_violation THEN > UPDATE item_details SET > title = ititle, > author = iauthor, > binding = ibinding, > list_price = ilist_price, > dimensions = y > WHERE isbn = iisbn; > END; > BEGIN > INSERT INTO amazon_items > (asin, sales_rank, offers, offer_pages, isbn) > VALUES > (iasin, isales_rank, crunch(ioffers), ioffer_pages, iisbn); > EXCEPTION WHEN unique_violation THEN > IF isales_rank IS NOT NULL THEN > UPDATE amazon_items SET > sales_rank = isales_rank > WHERE asin = iasin; > END IF; > IF ioffers IS NOT NULL THEN > UPDATE amazon_items SET > offers = crunch(ioffers), > offers_last_updated = CURRENT_TIMESTAMP, > offer_pages = ioffer_pages > WHERE asin = iasin; > END IF; > END; > END; > $$ > LANGUAGE plpgsql; > Hi, did the index on isbn field help? Another note, that is more fine tuning actually, then the real cause of the slow execution of your procedure. If you are expecting to update more, then insert, then you probably should not wait for the exception to be thrown as all the BEGIN EXCEPTION END blocks are more expensive to execute, then simple calls. Have a look here: http://www.postgresql.org/docs/8.3/interactive/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING Also note that if you UPDATE first, and then try to INSERT only when UPDATE could not find anything to update, you really HAVE to expect INSERT to fail and then retry updating, as another, parallel transaction, could be fast enough to INSERT a record after you tried to update and before your transaction starts to insert. With best regards, -- Valentine Gogichashvili