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.
