X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.130]) by svr1.postgresql.org (Postfix) with ESMTP id CE332D1DA60 for ; Fri, 29 Aug 2003 08:10:56 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (neptune.hub.org [64.117.224.130]) (amavisd-new, port 10024) with ESMTP id 98438-10 for ; Fri, 29 Aug 2003 05:10:48 -0300 (ADT) Received: from houston.familyhealth.com.au (unknown [203.59.48.253]) by svr1.postgresql.org (Postfix) with ESMTP id 615CFD1DA5F for ; Fri, 29 Aug 2003 05:10:44 -0300 (ADT) Received: from mars (work-40.internal [192.168.0.40]) by houston.familyhealth.com.au (8.12.9/8.12.9) with SMTP id h7T8AWLt041701; Fri, 29 Aug 2003 16:10:32 +0800 (WST) (envelope-from chriskl@familyhealth.com.au) Message-ID: <0be001c36e05$83034fb0$2800a8c0@mars> From: "Christopher Kings-Lynne" To: "Alexander Priem" , "Tomasz Myrta" Cc: References: <012f01c36dfa$0f4a8760$b696a8c0@APR> <3F4EFAB6.7030104@klaster.net> <015f01c36e02$134fe960$b696a8c0@APR> Subject: Re: Indexing question Date: Fri, 29 Aug 2003 16:14:04 +0800 MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2800.1158 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165 X-Virus-Scanned: by amavisd-new at postgresql.org X-Spam-Status: No, hits=0.5 tagged_above=0.0 required=5.0 tests=QUOTED_EMAIL_TEXT, RCVD_IN_OSIRUSOFT_COM, REFERENCES X-Spam-Level: X-Archive-Number: 200308/479 X-Sequence-Number: 3295 > So if I understand correctly I could ditch the 'deleted' field entirely and > use just the 'deleteddate' field. This 'deleteddate' field would be NULL by > default. It would contain a date value if the record is considered > 'deleted'. > > The index would be 'create index a on tablename(deleteddate) where > deleteddate is null'. > > I could then access 'current' records with a view like 'create view x_view > as select * from tablename where deleteddate is null'. > > Is that correct? This would be the best performing solution for this kind of > thing, I think (theoretically at least)? Yes, I think it would be best. Definitely better than your current solution. Cheers, Chris