From: Christopher Kings-Lynne <chriskl@familyhealth.com.au>
To: Alexander Priem <ap@cict.nl>
To: pgsql-performance@postgresql.org
Subject: Re: Indexing question
Date: Fri, 29 Aug 2003 15:05:52 +0800
Message-ID: <0b5e01c36dfb$fbaca380$2800a8c0@mars> (raw)
References: <012f01c36dfa$0f4a8760$b696a8c0@APR>
Remember to consider partial indexes:
eg. CREATE INDEX ON table (col) WHERE deletedate IS NOT NULL
Chris
----- Original Message -----
From: Alexander Priem
To: pgsql-performance@postgresql.org
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: chriskl@familyhealth.com.au, ap@cict.nl
Subject: Re: Indexing question
In-Reply-To: <0b5e01c36dfb$fbaca380$2800a8c0@mars>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox