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 CD38ED1B8B3 for ; Fri, 29 Aug 2003 06:50: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 92533-09 for ; Fri, 29 Aug 2003 03:50:16 -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 230E1D1B897 for ; Fri, 29 Aug 2003 03:50:14 -0300 (ADT) Received: from APR ([192.168.150.182]) by mail.cict.nl (Merak 6.0.5) with SMTP id DUC73886 for ; Fri, 29 Aug 2003 08:50:12 +0200 Message-ID: <012f01c36dfa$0f4a8760$b696a8c0@APR> From: "Alexander Priem" To: Subject: Indexing question Date: Fri, 29 Aug 2003 08:52:06 +0200 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_012C_01C36E0A.D2C8A900" 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-Spam-Status: No, hits=1.2 tagged_above=0.0 required=5.0 tests=HTML_30_40, HTML_FONT_BIG, HTML_FONT_COLOR_BLUE, HTML_FONT_FACE_ODD X-Spam-Level: * X-Archive-Number: 200308/469 X-Sequence-Number: 3285 This is a multi-part message in MIME format. ------=_NextPart_000_012C_01C36E0A.D2C8A900 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable 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 fo= r '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 did this this because I read somewhere that fields that can contain NULL = values will NOT be indexed. Is this true? Or could I ditch the 'deleted' field and just set 'deleteddate' to NULL by = default and to a DATE in the case of a deleted record? I could then index t= he field 'deleteddate' and create a view with where clause 'Where deletedda= te is null'. Would this give the same performance as my current solution (with an indexe= d boolean field 'deleted') ? I cannot test this myself at the moment as I am still in a design phase and= do not have a real server available yet... Thanks in advance, Alexander Priem ------=_NextPart_000_012C_01C36E0A.D2C8A900 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
Hi all,
 
I have some tables (which can get pretty l= arge) in=20 which I want to record 'current' data as well as 'historical' data. Th= is=20 table has fields 'deleted' and 'deleteddate' (among other fields, of course= ).=20 The field 'deleted' is false be default. Every record that I want to delete= gets=20 the value true for 'deleted' and 'deleteddate' is set to the date of=20 deletion.
 
Since these tables are used a lot by queri= es that=20 only use 'current' data, I have created a view with a where clause 'Where n= ot=20 deleted'. Also, I have indexed field 'deleted'.
 
I did this this because I read somewhere t= hat=20 fields that can contain NULL values will NOT be indexed.
 
Is this true?
 
Or could I ditch the 'deleted' field and j= ust set=20 'deleteddate' to NULL by default and to a DATE in the case of a deleted rec= ord?=20 I could then index the field 'deleteddate' and create a view with where cla= use=20 'Where deleteddate is null'.
 
Would this give the same performance as my= current=20 solution (with an indexed boolean field 'deleted') ?
 
I cannot test this myself at the moment as= I am=20 still in a design phase and do not have a real server available=20 yet...
 
Thanks in advance,

Alexander Priem
------=_NextPart_000_012C_01C36E0A.D2C8A900--