So if I understand correctly I could ditch the 'deleted' field entirely and
use just the 'deleteddate' field. This 'deleteddate' field would be NULL by
default. It would contain a date value if the record is considered
'deleted'.
The index would be 'create index a on tablename(deleteddate) where
deleteddate is null'.
I could then access 'current' records with a view like 'create view x_view
as select * from tablename where deleteddate is null'.
Is that correct? This would be the best performing solution for this kind of
thing, I think (theoretically at least)?
Kind regards,
Alexander Priem.
----- Original Message -----
From: "Tomasz Myrta" <jasiek@klaster.net>
To: "Alexander Priem" <ap@cict.nl>
Cc: <pgsql-performance@postgresql.org>
Sent: Friday, August 29, 2003 9:03 AM
Subject: Re: [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'.
>
> <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
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend