Re: Indexing question - Mailing list pgsql-performance

From Christopher Kings-Lynne
Subject Re: Indexing question
Date
Msg-id 0b5e01c36dfb$fbaca380$2800a8c0@mars
Whole thread Raw
In response to Indexing question  ("Alexander Priem" <ap@cict.nl>)
List pgsql-performance
Remember to consider partial indexes:
 
eg. CREATE INDEX ON table (col) WHERE deletedate IS NOT NULL
 
Chris
----- Original Message -----
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 '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 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 the field 'deleteddate' and create a view with where clause 'Where deleteddate is null'.
 
Would this give the same performance as my current solution (with an indexed 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

pgsql-performance by date:

Previous
From: Ken Geis
Date:
Subject: Re: bad estimates
Next
From: Tomasz Myrta
Date:
Subject: Re: Indexing question