DELETE with filter on ctid - Mailing list pgsql-performance

From Spiegelberg, Greg
Subject DELETE with filter on ctid
Date
Msg-id 82E74D266CB9B44390D3CCE44A781ED90B6ADC@POSTOFFICE.cranel.local
Whole thread Raw
Responses Re: DELETE with filter on ctid  (Tom Lane <tgl@sss.pgh.pa.us>)
Re: DELETE with filter on ctid  ("Craig A. James" <cjames@modgraph-usa.com>)
List pgsql-performance
We have a query which generates a small set of rows (~1,000) which are to be used in a DELETE on the same table.  The problem we have is that we need to join on 5 different columns and it takes far too long. 
I have a solution but I'm not sure it's the right one.  Instead of joining on 5 columns in the DELETE the join uses the ctid column.
 
BEGIN;
CREATE INDEX gregs_table_ctid_idx ON gregs_table(ctid);
DELETE FROM gregs_table gt
   USING (SELECT ctid FROM gregs_table WHERE ...) as s
   WHERE gt.ctid=s.ctid;
DROP INDEX gregs_table_ctid_idx;
COMMIT;
 
The difference to me is a 20+ minute to a ~5 second transaction.  The table is loaded using COPY, never INSERT, never UPDATE'd.  COPY, SELECT and DELETE is its life.  PostgreSQL 8.2.1 on RedHat ES 4.0 is the target platform.
 
Any possible issues with using ctid in the DELETE and transaction?  I understand ctid is "useless" in the long run as the documentation points out but for the short term and within a transaction it seems to work well.
 
Thoughts?
 
Greg
 
 
--
 Greg Spiegelberg
 614.318.4314, office
 614.431.8388, fax
 ISOdx Product Development Manager
 Cranel, Inc.
 
 

pgsql-performance by date:

Previous
From: "Alex Deucher"
Date:
Subject: Re: postgres 8.2 seems to prefer Seq Scan
Next
From: Carlos Moreno
Date:
Subject: Please humor me ...