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

Indexing question

From: "Alexander Priem" <ap(at)cict(dot)nl>
To: <pgsql-performance(at)postgresql(dot)org>
Subject: Indexing question
Date: 2003-08-29 06:52:06
Message-ID: 012f01c36dfa$0f4a8760$b696a8c0@APR (view raw or flat)
Thread:
Lists: pgsql-performance
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

Responses

pgsql-performance by date

Next:From: Ken GeisDate: 2003-08-29 07:01:09
Subject: Re: bad estimates
Previous:From: Bruno Wolff IIIDate: 2003-08-29 04:24:53
Subject: Re: bad estimates

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