Re: Terrible Write Performance of a Stored Procedure

On Jun 26, 9:30 pm, goofyheadedp(dot)(dot)(dot)(at)gmail(dot)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