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:59:10
Message-ID: 3F4F07CE.6090803@klaster.net (view raw or flat)
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.

Near, but not exactly. You don't need field deleted - it's true.

Your example:
create index a on tablename(deleteddate) where deleteddate is null
we can translate to:
create index a on tablename(NULL) where deleteddate is null
which doesn't make too much sense.

Check your queries. You probably have something like this:
select * from tablename where not deleted and xxx

Create your index to match xxx clause - if xxx is "some_id=13", then 
create your index as:
create index on tablename(some_id) where deleteddate is null;

Regards,
Tomasz Myrta


In response to

Responses

pgsql-performance by date

Next:From: Ken GeisDate: 2003-08-29 08:10:06
Subject: Re: bad estimates
Previous:From: Ken GeisDate: 2003-08-29 07:57:43
Subject: Re: bad estimates

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