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:59:10 +0200
Message-ID: <3F4F07CE.6090803@klaster.net> (raw)
In-Reply-To: <015f01c36e02$134fe960$b696a8c0@APR>
References: <012f01c36dfa$0f4a8760$b696a8c0@APR>
<3F4EFAB6.7030104@klaster.net>
<015f01c36e02$134fe960$b696a8c0@APR>
> 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
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: <3F4F07CE.6090803@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