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
gspiegelberg(at)cranel(dot)com <mailto:gspiegelberg(at)cranel(dot)com>
614.318.4314, office
614.431.8388, fax
ISOdx Product Development Manager
Cranel, Inc.