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

Re: Indexing question

From: Tomasz Myrta <jasiek(at)klaster(dot)net>
To: Alexander Priem <ap(at)cict(dot)nl>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Indexing question
Date: 2003-08-29 07:03:18
Message-ID: 3F4EFAB6.7030104@klaster.net (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'.

<cut>
I think the best choice for your case is using conditional indexes. It 
should be much better than indexing 'deleted' field. I don't know on 
which exactly fields you have to create this index - you have to check 
it by yourself - what do you have in "where" clause?

Example:
create index some_index on your_table(id_field) where not deleted;


Regards,
Tomasz Myrta


In response to

Responses

pgsql-performance by date

Next:From: William YuDate: 2003-08-29 07:05:03
Subject: Re: Hardware recommendations to scale to silly load
Previous:From: Ken GeisDate: 2003-08-29 07:01:09
Subject: Re: bad estimates

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