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 A422DD1B8AB for ; Fri, 29 Aug 2003 08:39:23 +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 01251-09 for ; Fri, 29 Aug 2003 05:39:14 -0300 (ADT) Received: from mail.cict.nl (vnd-7521.mxs.adsl.euronet.nl [62.234.149.33]) by svr1.postgresql.org (Postfix) with ESMTP id 00DC2D1B8B3 for ; Fri, 29 Aug 2003 05:39:12 -0300 (ADT) Received: from APR ([192.168.150.182]) by mail.cict.nl (Merak 6.0.5) with SMTP id DUC73886; Fri, 29 Aug 2003 10:39:11 +0200 Message-ID: <018c01c36e09$4893e980$b696a8c0@APR> From: "Alexander Priem" To: "Tomasz Myrta" Cc: References: <012f01c36dfa$0f4a8760$b696a8c0@APR> <3F4EFAB6.7030104@klaster.net> <015f01c36e02$134fe960$b696a8c0@APR> <3F4F07CE.6090803@klaster.net> Subject: Re: Indexing question Date: Fri, 29 Aug 2003 10:41:04 +0200 MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2800.1106 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1106 X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200308/484 X-Sequence-Number: 3300 I think I understand what you mean :) Let's see if that's true : The entire table WAS like this: (just one example table, I have many more) create table orderadvice ( orad_id serial primary key, orad_name varchar(25) unique not null, orad_description varchar(50) default null, orad_value integer not null default 0, orad_value_quan integer references quantity (quan_id) not null default 0, orad_deleted boolean not null default false, orad_deleteddate date default null, orad_deletedby integer references systemuser (user_id) default null ) without oids; Indexes were like this: create index orad_deleted_index on orderadvice (orad_deleted); (orad_id and orad_name indexed implicitly in the create table statement) A view on this table: create view orderadvice_edit as select orad_id,orad_name,orad_description,orad_value,orad_value_quan from orderadvice where not orad_deleted; Most queries on this view would be like 'select * from orderadvice_edit where orad_id=100' or 'select * from orderadvice_edit order by orad_name'. How about the following script. Would it be better, given the type of queries that would take place on this table? create table orderadvice ( orad_id serial primary key, orad_name varchar(25) not null, orad_description varchar(50) default null, orad_value integer not null default 0, orad_value_quan integer references quantity (quan_id) not null default 0, orad_deleteddate date default null, orad_deletedby integer references systemuser (user_id) default null ) without oids; create index orad_id_index on orderadvice (orad_id) where orad_deleteddate is null; create index orad_name_index on orderadvice (orad_name) where orad_deleteddate is null; create view orderadvice_edit as select orad_id,orad_name,orad_description,orad_value,orad_value_quan from orderadvice where orad_deleteddate is null; Would queries like 'select * from orderadvice_edit where orad_id=100' or 'select * from orderadvice_edit order by orad_name' both use one of these two partial indexes, given enough records are present in the table? There would be a double index on the primary key this way, right? Thanks for your advice so far, Alexander Priem. ----- Original Message ----- From: "Tomasz Myrta" To: "Alexander Priem" Cc: Sent: Friday, August 29, 2003 9:59 AM Subject: Re: [PERFORM] Indexing question > > 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 >