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 607B7D1B8B3 for ; Fri, 29 Aug 2003 07:03:07 +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 94917-07 for ; Fri, 29 Aug 2003 04:02:36 -0300 (ADT) Received: from houston.familyhealth.com.au (unknown [203.59.48.253]) by svr1.postgresql.org (Postfix) with ESMTP id ABCD6D1B8CC for ; Fri, 29 Aug 2003 04:02:32 -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 h7T72LLt040433; Fri, 29 Aug 2003 15:02:21 +0800 (WST) (envelope-from chriskl@familyhealth.com.au) Message-ID: <0b5e01c36dfb$fbaca380$2800a8c0@mars> From: "Christopher Kings-Lynne" To: "Alexander Priem" , References: <012f01c36dfa$0f4a8760$b696a8c0@APR> Subject: Re: Indexing question Date: Fri, 29 Aug 2003 15:05:52 +0800 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0B5B_01C36E3F.09C3FCA0" 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.3 tagged_above=0.0 required=5.0 tests=BAYES_30, HTML_30_40, HTML_FONT_BIG, HTML_FONT_COLOR_BLUE, HTML_FONT_FACE_ODD, REFERENCES X-Spam-Level: X-Archive-Number: 200308/471 X-Sequence-Number: 3287 This is a multi-part message in MIME format. ------=_NextPart_000_0B5B_01C36E3F.09C3FCA0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Remember to consider partial indexes: eg. CREATE INDEX ON table (col) WHERE deletedate IS NOT NULL Chris ----- Original Message -----=20 From: Alexander Priem=20 To: pgsql-performance@postgresql.org=20 Sent: Friday, August 29, 2003 2:52 PM Subject: [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 'delete= d' and 'deleteddate' (among other fields, of course). The field 'deleted' i= s 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 ha= ve indexed field 'deleted'. I did this this because I read somewhere that fields that can contain NUL= L values will NOT be indexed. Is this true? Or could I ditch the 'deleted' field and just set 'deleteddate' to NULL b= y default and to a DATE in the case of a deleted record? I could then index= the field 'deleteddate' and create a view with where clause 'Where deleted= date is null'. Would this give the same performance as my current solution (with an inde= xed boolean field 'deleted') ? I cannot test this myself at the moment as I am still in a design phase a= nd do not have a real server available yet... Thanks in advance, Alexander Priem ------=_NextPart_000_0B5B_01C36E3F.09C3FCA0 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
Remember to consider partial indexes:
 
eg. CREATE INDEX ON table (col) WHERE deletedate IS NOT= =20 NULL
 
Chris
----- Original Message -----
Fro= m:=20 Alexander Priem
To: pgsql-performance@postgr= esql.org=20
Sent: Friday, August 29, 2003 2:52= =20 PM
Subject: [PERFORM] Indexing=20 question

Hi all,
 
I have some tables (which can get pretty= large)=20 in which I want to record 'current' data as well as 'historical' dat= a.=20 This table has fields 'deleted' and 'deleteddate' (among other fields, of= =20 course). The field 'deleted' is false be default. Every record that I wan= t to=20 delete gets the value true for 'deleted' and 'deleteddate' is set to the = date=20 of deletion.
 
Since these tables are used a lot by que= ries that=20 only use 'current' data, I have created a view with a where clause 'Where= not=20 deleted'. Also, I have indexed field 'deleted'.
 
I did this this because I read somewhere= that=20 fields that can contain NULL values will NOT be indexed.
 
Is this true?
 
Or could I ditch the 'deleted' field and= just set=20 'deleteddate' to NULL by default and to a DATE in the case of a deleted= =20 record? I could then index the field 'deleteddate' and create a view with= =20 where clause 'Where deleteddate is null'.
 
Would this give the same performance as = my=20 current solution (with an indexed boolean field 'deleted') ?<= /DIV>
 
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_0B5B_01C36E3F.09C3FCA0--