Skip site navigation (1) Skip section navigation (2)

Re: Indexing question

From: "Christopher Kings-Lynne" <chriskl(at)familyhealth(dot)com(dot)au>
To: "Alexander Priem" <ap(at)cict(dot)nl>,<pgsql-performance(at)postgresql(dot)org>
Subject: Re: Indexing question
Date: 2003-08-29 07:05:52
Message-ID: 0b5e01c36dfb$fbaca380$2800a8c0@mars (view raw, whole thread or download thread mbox)
Lists: pgsql-performance
Remember to consider partial indexes:

eg. CREATE INDEX ON table (col) WHERE deletedate IS NOT NULL

  ----- Original Message ----- 
  From: Alexander Priem 
  To: pgsql-performance(at)postgresql(dot)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

In response to

pgsql-performance by date

Next:From: Christopher Kings-LynneDate: 2003-08-29 07:30:06
Subject: Re: Hardware recommendations to scale to silly load
Previous:From: William YuDate: 2003-08-29 07:05:03
Subject: Re: Hardware recommendations to scale to silly load

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group