Re: Indexing question - Mailing list pgsql-performance
From | Alexander Priem |
---|---|
Subject | Re: Indexing question |
Date | |
Msg-id | 018c01c36e09$4893e980$b696a8c0@APR Whole thread Raw |
In response to | Indexing question ("Alexander Priem" <ap@cict.nl>) |
Responses |
Re: Indexing question
|
List | pgsql-performance |
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" <jasiek@klaster.net> To: "Alexander Priem" <ap@cict.nl> Cc: <pgsql-performance@postgresql.org> 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 >
pgsql-performance by date: