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 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
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
From | Date | Subject | |
---|---|---|---|
Next Message | William Yu | 2003-08-29 07:05:03 | Re: Hardware recommendations to scale to silly load |
Previous Message | Ken Geis | 2003-08-29 07:01:09 | Re: bad estimates |