From: Tomasz Myrta <jasiek@klaster.net>
To: Alexander Priem <ap@cict.nl>
Cc: pgsql-performance@postgresql.org
Subject: Re: Indexing question
Date: Fri, 29 Aug 2003 09:03:18 +0200
Message-ID: <3F4EFAB6.7030104@klaster.net> (raw)
In-Reply-To: <012f01c36dfa$0f4a8760$b696a8c0@APR>
References: <012f01c36dfa$0f4a8760$b696a8c0@APR>
> 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
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: jasiek@klaster.net, ap@cict.nl
Subject: Re: Indexing question
In-Reply-To: <3F4EFAB6.7030104@klaster.net>
* 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