Received: from mailbox4.ucsd.edu (mailbox4.ucsd.edu [132.239.1.56]) by postgresql.org (Postfix) with ESMTP id 51AB847597B for ; Thu, 21 Mar 2002 18:29:38 -0500 (EST) Received: from mail.ucsd.edu (ucsd.ucsd.edu [132.239.1.1]) by mailbox4.ucsd.edu (8.12.1/8.12.1) with ESMTP id g2LNTXFs017972; Thu, 21 Mar 2002 15:29:33 -0800 (PST) Received: from there (dyn128-54-156-8.ucsd.edu [128.54.156.8]) by mail.ucsd.edu; id PAA17841 sendmail 8.8.8AS/UCSD8.3 via SMTP Thu, 21 Mar 2002 15:29:33 -0800 ( PST) Message-Id: <200203212329.PAA17841@mail.ucsd.edu> Content-Type: text/plain; charset="iso-8859-1" From: Jeff Davis To: Dmitry Tkach , pgsql-general@postgresql.org Subject: Re: A general database question! Date: Thu, 21 Mar 2002 15:25:19 -0800 X-Mailer: KMail [version 1.3.2] References: <3C9A4759.6000607@openratings.com> In-Reply-To: <3C9A4759.6000607@openratings.com> MIME-Version: 1.0 Content-Transfer-Encoding: 8bit X-Archive-Number: 200203/1097 X-Sequence-Number: 22460 > I have a general database design question for you... > Suppose, I have a database of entities, that have two attributes, say, > location and name. Location is numerical and name is a varchar. > I need to be able to search by both location and name (e.g. get all the > entries whose location is 1 and name is John etc...). > It is simple so far - I'd just create an an index on both location and > name... > I am not quite understanding why a multi-column index won't work. Can you explain a little more? Here is what I was thinking: create table entity( type int, location int, name varchar(100) not null, ); create unique index my_index on entity(location,name); It seems as though an "and" query would run quickly. It also seems as though if you needed to get a few records that were one of several names, a query such as the following would run quickly: select * from entity where location = 5 and (name = 'joe' or name='joseph'); or are the aliases a seperate attribute? I am not clear on exactly where the alias comes in. Regards, Jeff