Re: Indexing question

From: "Alexander Priem" <ap(at)cict(dot)nl>
To: "Tomasz Myrta" <jasiek(at)klaster(dot)net>
Cc: <pgsql-performance(at)postgresql(dot)org>
Subject: Re: Indexing question
Date: 2003-08-29 07:49:28
Message-ID: 015f01c36e02$134fe960$b696a8c0@APR
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

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(at)klaster(dot)net>
To: "Alexander Priem" <ap(at)cict(dot)nl>
Cc: <pgsql-performance(at)postgresql(dot)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

In response to

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Ken Geis 2003-08-29 07:57:43 Re: bad estimates
Previous Message Christopher Kings-Lynne 2003-08-29 07:30:06 Re: Hardware recommendations to scale to silly load