pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Aaron Werman <awerman2@hotmail.com>
To: pgsql-performance@postgresql.org
To: Patrick Hatcher <PHatcher@macys.com>
Subject: Re: Slow update/insert process
Date: Fri, 1 Oct 2004 15:48:50 -0400
Message-ID: <BAY18-DAV56WVY5jVkj000034b4@hotmail.com> (raw)
References: <OF3AE712A1.C7740C8F-ON85256F20.0064BEE6-88256F20.006559A1@fds.com>

Some quick notes:

- Using a side effect of a function to update the database feels bad to me
- how long does the  SELECT into varQueryRecord md5(upc.keyp....
  function take / what does it's explain look like?
- There are a lot of non-indexed columns on that delta master table, such as keyf_upc. 
   I'm guessing you're doing 90,000 x {a lot of slow scans}
- My temptation would be to rewrite the processing to do a pass of updates, a pass of inserts, 
  and then the SELECT
  ----- Original Message ----- 
  From: Patrick Hatcher 
  To: pgsql-performance@postgresql.org 
  Sent: Friday, October 01, 2004 2:14 PM
  Subject: [PERFORM] Slow update/insert process



  Pg: 7.4.5 
  RH 7.3 
  8g Ram 
  200 g drive space 
  RAID0+1 
  Tables vacuum on a nightly basis 

  The following process below takes 8 hours to run on 90k records and I'm not sure where to being to look for the bottleneck.  This isn't the only updating on this database that seems to take a long time to complete.  Is there something I should be looking for in my conf settings?   

  TIA 
  Patrick 


  SQL: 
  ---Bring back only selected records to run through the update process. 
  --Without the function the SQL takes < 10secs to return 90,000 records 
  SELECT count(pm.pm_delta_function_amazon(upc.keyp_upc,'amazon')) 
  FROM mdc_upc upc 
  JOIN public.mdc_products prod ON upc.keyf_products = prod.keyp_products 
  JOIN public.mdc_price_post_inc price ON prod.keyp_products = price.keyf_product 
  JOIN public.mdc_attribute_product ap on ap.keyf_products = prod.keyp_products and keyf_attribute=22 
  WHERE 
  upper(trim(ap.attributevalue)) NOT IN ('ESTEE LAUDER', 'CLINIQUE','ORGINS','PRESCRIPTIVES','LANC?ME','CHANEL','ARAMIS','M.A.C','TAG HEUER') 
  AND keyf_producttype<>222 
  AND prod.action_publish = 1; 


  Function: 

  CREATE OR REPLACE FUNCTION pm.pm_delta_function_amazon(int4, "varchar")
   RETURNS bool AS
  'DECLARE
     varkeyf_upc                ALIAS FOR $1;
     varPassword                ALIAS FOR $2;
     varRealMD5                varchar;
     varDeltaMD5                varchar;
     varLastTouchDate        date;
     varQuery                 text;
     varQuery1                 text;
     varQueryMD5                text;
     varQueryRecord        record;
     varFuncStatus        boolean := false;
     
  BEGIN

  -- Check the password
   IF varPassword <> \'amazon\' THEN
     Return false;
   END IF;


  --  Get the md5 hash for this product
   SELECT into varQueryRecord md5(upc.keyp_upc || prod.description || pm.pm_price_post_inc(prod.keyp_products)) AS md5
     FROM public.mdc_upc upc
     JOIN public.mdc_products prod ON upc.keyf_products = prod.keyp_products
     JOIN public.mdc_price_post_inc price ON price.keyf_product = prod.keyp_products
     WHERE upc.keyp_upc = varkeyf_upc LIMIT 1 ;
   

   IF NOT FOUND THEN
     RAISE EXCEPTION \'varRealMD5 is NULL. UPC ID is %\', varkeyf_upc;
   ELSE
     varRealMD5:=varQueryRecord.md5;
   END IF;

  --  Check that the product is in the delta table and return its hash for comparison 
   SELECT into varQueryRecord md5_hash,last_touch_date 
     FROM pm.pm_delta_master_amazon
     WHERE keyf_upc = varkeyf_upc LIMIT 1;

   IF NOT FOUND THEN
     -- ADD and exit
     INSERT INTO pm.pm_delta_master_amazon (keyf_upc,status,md5_hash,last_touch_date)
     values (varkeyf_upc,\'add\',varRealMD5,CURRENT_DATE);
     varFuncStatus:=true;
     RETURN varFuncStatus;
   ELSE
     --Update the record 
       --- If the hash matches then set the record to HOLD
     IF varRealMD5 = varQueryRecord.md5_hash THEN
         UPDATE pm.pm_delta_master_amazon
           SET status= \'hold\',
           last_touch_date =  CURRENT_DATE
         WHERE keyf_upc =  varkeyf_upc AND last_touch_date <> CURRENT_DATE; 
         varFuncStatus:=true;
     ELSE
         --  ELSE mark the item as ADD
         UPDATE pm.pm_delta_master_amazon
           SET status= \'add\',
           last_touch_date =  CURRENT_DATE
         WHERE keyf_upc =  varkeyf_upc;
         varFuncStatus:=true;
     END IF;
    END IF;

   RETURN varFuncStatus;
  END;'
   LANGUAGE 'plpgsql' IMMUTABLE;



  TableDef 
  CREATE TABLE pm.pm_delta_master_amazon ( 
      keyf_upc               int4 , 
      status                 varchar(6) , 
      md5_hash               varchar(40) , 
      last_touch_date        date 
      ) 
  GO 

  CREATE INDEX status_idx 
      ON pm.pm_delta_master_amazon(status) 
  GO 




  CONF 
  -------- 
  # WRITE AHEAD LOG 
  #--------------------------------------------------------------------------- 

  # - Settings - 

  #fsync = true                   # turns forced synchronization on or off 
  #wal_sync_method = fsync        # the default varies across platforms: 
                                  # fsync, fdatasync, open_sync, or open_datasync 
  wal_buffers = 32                # min 4, 8KB each 

  # - Checkpoints - 

  checkpoint_segments = 50        # in logfile segments, min 1, 16MB each 
  checkpoint_timeout = 600        # range 30-3600, in seconds 
  #checkpoint_warning = 30        # 0 is off, in seconds 
  #commit_delay = 0               # range 0-100000, in microseconds 
  #commit_siblings = 5            # range 1-1000 


  Patrick Hatcher
  Macys.Com

view thread (3+ messages)  latest in thread

Message-ID: <BAY18-DAV56WVY5jVkj000034b4@hotmail.com>
Permalink:  ../BAY18-DAV56WVY5jVkj000034b4@hotmail.com/
Also on:    postgresql.org/message-id/BAY18-DAV56WVY5jVkj000034b4@hotmail.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: awerman2@hotmail.com, PHatcher@macys.com
  Subject: Re: Slow update/insert process
  In-Reply-To: <BAY18-DAV56WVY5jVkj000034b4@hotmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox