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 EE363D1B8AB for ; Fri, 29 Aug 2003 07:47:47 +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 97105-09 for ; Fri, 29 Aug 2003 04:47:38 -0300 (ADT) Received: from mail.cict.nl (vnd-7521.mxs.adsl.euronet.nl [62.234.149.33]) by svr1.postgresql.org (Postfix) with ESMTP id 32D49D1B8A4 for ; Fri, 29 Aug 2003 04:47:36 -0300 (ADT) Received: from APR ([192.168.150.182]) by mail.cict.nl (Merak 6.0.5) with SMTP id DUC73886; Fri, 29 Aug 2003 09:47:35 +0200 Message-ID: <015f01c36e02$134fe960$b696a8c0@APR> From: "Alexander Priem" To: "Tomasz Myrta" Cc: References: <012f01c36dfa$0f4a8760$b696a8c0@APR> <3F4EFAB6.7030104@klaster.net> Subject: Re: Indexing question Date: Fri, 29 Aug 2003 09:49:28 +0200 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.1106 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1106 X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200308/475 X-Sequence-Number: 3291 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)? Kind regards, Alexander Priem. ----- Original Message ----- From: "Tomasz Myrta" To: "Alexander Priem" Cc: Sent: Friday, August 29, 2003 9:03 AM Subject: Re: [PERFORM] Indexing question > > Hi all, > > > > I have some tables (which can get pretty large) in which I want to > > record 'current' data as well as 'historical' data. This table has > > fields 'deleted' and 'deleteddate' (among other fields, of course). The > > field 'deleted' is false be default. Every record that I want to delete > > gets the value true for 'deleted' and 'deleteddate' is set to the date > > of deletion. > > > > Since these tables are used a lot by queries that only use 'current' > > data, I have created a view with a where clause 'Where not deleted'. > > Also, I have indexed field 'deleted'. > > > I think the best choice for your case is using conditional indexes. It > should be much better than indexing 'deleted' field. I don't know on > which exactly fields you have to create this index - you have to check > it by yourself - what do you have in "where" clause? > > Example: > create index some_index on your_table(id_field) where not deleted; > > > Regards, > Tomasz Myrta > > > ---------------------------(end of broadcast)--------------------------- > TIP 8: explain analyze is your friend