X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.130]) by svr1.postgresql.org (Postfix) with ESMTP id B0CB1D1B8B1 for ; Fri, 29 Aug 2003 07:59:42 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (neptune.hub.org [64.117.224.130]) (amavisd-new, port 10024) with ESMTP id 00480-04 for ; Fri, 29 Aug 2003 04:59:12 -0300 (ADT) Received: from serwer.skawsoft.com.pl (serwer.skawsoft.com.pl [213.25.37.66]) by svr1.postgresql.org (Postfix) with ESMTP id 3EB3BD1B9AB for ; Fri, 29 Aug 2003 04:59:10 -0300 (ADT) Received: from klaster.net (core-1.citynet.pl [80.48.135.69]) by serwer.skawsoft.com.pl (Postfix) with ESMTP id 5D5AD2B3A4; Fri, 29 Aug 2003 09:58:59 +0200 (CEST) Message-ID: <3F4F07CE.6090803@klaster.net> Date: Fri, 29 Aug 2003 09:59:10 +0200 From: Tomasz Myrta User-Agent: Mozilla/5.0 (Windows; U; Win 9x 4.90; pl-PL; rv:1.5a) Gecko/20030718 X-Accept-Language: pl, en-us, en MIME-Version: 1.0 To: Alexander Priem Cc: pgsql-performance@postgresql.org Subject: Re: Indexing question References: <012f01c36dfa$0f4a8760$b696a8c0@APR> <3F4EFAB6.7030104@klaster.net> <015f01c36e02$134fe960$b696a8c0@APR> In-Reply-To: <015f01c36e02$134fe960$b696a8c0@APR> Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200308/477 X-Sequence-Number: 3293 > 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