X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id F3B9ED1D08B for ; Tue, 20 Apr 2004 08:15:58 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 80842-02 for ; Tue, 20 Apr 2004 08:16:00 -0300 (ADT) Received: from biglumber.com (biglumber.com [207.228.252.42]) by svr1.postgresql.org (Postfix) with SMTP id 520C4D1B482 for ; Tue, 20 Apr 2004 08:15:56 -0300 (ADT) Received: (qmail 24833 invoked from network); 20 Apr 2004 11:15:58 -0000 Received: from unknown (HELO localhost) (207.228.252.42) by 0 with SMTP; 20 Apr 2004 11:15:58 -0000 From: "Greg Sabino Mullane" To: pgsql-performance@postgresql.org Subject: Re: Deleting certain duplicates X-PGP-Key: 2529 DF6A B8F7 9407 E944 45B4 BC9B 9067 1496 4AC8 X-Request-PGP: http://www.biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 In-Reply-To: Date: Tue, 20 Apr 2004 11:15:58 -0000 X-Mailer: JoyMail 1.47 Message-ID: <82428c807d3c47fbcd0c013dbcab12b5@biglumber.com> X-Virus-Scanned: by amavisd-new at postgresql.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200404/291 X-Sequence-Number: 6591 -----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 > So I was thinking maybe of doing the deletion in chunks, perhaps based on > reception time. > Are there any suggestions for a better way to do this, or using multiple > queries to delete selectively a week at a time based on the reception_time. > I would say there are a lot of duplicate entries between mid march to the > first week of April. You are on the right track, in that dividing up the table will help. However, you cannot divide on the reception_time as that is the unique column. Analyze your data and divide on a row with a fairly uniform distribution over the time period in question. Then copy a segment out, clean it up, and put it back in. Make sure there is an index on the column in question, of course. For example, if 1/10 of the table has a "units" of 12, you could do something like this: CREATE INDEX units_dev ON forecastelement (units); CREATE TEMPORARY TABLE units_temp AS SELECT * FROM forecastelement WHERE units='12'; CREATE INDEX units_oid_index ON units_temp(oid); (Delete out duplicate rows from units_temp using your previous query or something else) DELETE FROM forecastelement WHERE units='12'; INSERT INTO forecastelement SELECT * FROM units_temp; DELETE FROM units_temp; Repeat as needed until all rows are done. Subsequent runs can be done by doing a INSERT INTO units_temp SELECT * FROM forecastelement WHERE units='...' and skipping the CREATE INDEX steps. On the other hand, your original deletion query may work as is, with the addition of an oid index. Perhaps try an EXPLAIN on it. - -- Greg Sabino Mullane greg@turnstep.com PGP Key: 0x14964AC8 200404200706 -----BEGIN PGP SIGNATURE----- iD8DBQFAhQVWvJuQZxSWSsgRAvLEAKDCVcX3Llm8JgszI/BBC1SobtjVawCfVGKu ERcV5J2JolwgZRhMbXnNM90= =JqET -----END PGP SIGNATURE-----