"Spiegelberg, Greg" <gspiegelberg@xxxxxxxxxx> writes: > 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; Forget the index, it's useless here (hint: ctid is a physical address). I'm wondering though why you don't just transpose the subquery's WHERE condition into the DELETE's WHERE? Or is this example oversimplified? regards, tom lane
