Received: from ext1.ics.forth.gr (ext1.ics.forth.gr [139.91.151.10]) by hub.org (8.10.1/8.10.1) with ESMTP id e73HppG25713 for ; Thu, 3 Aug 2000 13:51:51 -0400 (EDT) Received: from ismene.ics.forth.gr (mailhost.ics.forth.gr [139.91.157.51]) by ext1.ics.forth.gr (8.9.3/ICS-FORTH/V8.2.5-GATE) with ESMTP id UAA01337 for ; Thu, 3 Aug 2000 20:51:47 +0300 (EET DST) Received: from sappho.ics.forth.gr (sappho-lane.ics.forth.gr [139.91.157.50]) by ismene.ics.forth.gr (8.8.8/ICS-FORTH/V3) with ESMTP id UAA25616 for ; Thu, 3 Aug 2000 20:51:45 +0300 (EET DST) Received: from localhost (alexaki@localhost) by sappho.ics.forth.gr (8.8.8/ICS-FORTH/C1) with ESMTP id UAA25260 for ; Thu, 3 Aug 2000 20:51:44 +0300 (EET DST) Posted-Date: Thu, 3 Aug 2000 20:51:44 +0300 (EET DST) X-Authentication-Warning: sappho.ics.forth.gr: alexaki owned process doing -bs Organization: Date: Thu, 3 Aug 2000 20:51:44 +0300 (EET DST) From: Alexaki Sofia To: pgsql-sql@postgresql.org Subject: A question about indexes... Message-ID: MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Archive-Number: 200008/29 Hello, I have the following tables in my db Painter (id integer, uri varchar(256)) and paints (id1 integer, id2 integer) I want to optimize the question select id from Painter where uri = 'xxxxx'; What kind of index (Btree or Hash) is more efficient to create on field uri since it's a string? I also want to optimize the join between the ables Painter and paints on the fields id and id1 respectively? I can either define the field id as a Primary Key or create an Btree index on it. What is more effient?? From my test I see that creating Btree index is a bit faster!!. Would the performance (of the join) be improved if I created indexes both on field id and id1 or it's sufficient to create one of the two indexes ? As far as I can see the performance is improved if I have a Primary Key on Painter.id and a BTree index on paints.id1. However when I create a Btree index on Painter.id and a BTree index on paints.id1 performance gets worst. thank you in advance for your help Sofia Alexaki alexaki@ics.forth.gr