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 e74A2qG62766 for ; Fri, 4 Aug 2000 06:02:53 -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 NAA10317 for ; Fri, 4 Aug 2000 13:02:50 +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 NAA28476 for ; Fri, 4 Aug 2000 13:02:48 +0300 (EET DST) Received: from localhost (alexaki@localhost) by sappho.ics.forth.gr (8.8.8/ICS-FORTH/C1) with ESMTP id NAA00019 for ; Fri, 4 Aug 2000 13:02:47 +0300 (EET DST) Posted-Date: Fri, 4 Aug 2000 13:02:47 +0300 (EET DST) X-Authentication-Warning: sappho.ics.forth.gr: alexaki owned process doing -bs Organization: Date: Fri, 4 Aug 2000 13:02:47 +0300 (EET DST) From: Alexaki Sofia To: pgsql-sql@postgresql.org Subject: Re: A question about indexes... Message-ID: MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Archive-Number: 200008/38 Hello, I have the following tables in my database Painter(id integer, uri varchar(256)) paints(id1 integer, id2 integer) in order to speed up the join (select * from painter, paints where painter.id= paints.id1) between these two tables I have created indexes on the field painter.id and/or paints.id1. But as I see from the query plan the indexes are not used, instead sequential search is done either I define indexes or not. As you can see below the query plan remains the same. Is that reasonable??? Shouldn't Postgresql use the indexes in order to optimize question???I can't see why is better to make sequential search since the size of tables is relatively big. A) No indexes are defined on the tables Hash Join (cost=12269.78 rows=60014 width=24) -> Seq Scan on painter1 (cost=4234.97 rows=99999 width=16) -> Hash (cost=1931.92 rows=50331 width=8) -> Seq Scan on paints (cost=1931.92 rows=50331 width=8) B1) BTree index on painter.id Hash Join (cost=12269.78 rows=60014 width=24) -> Seq Scan on painter (cost=4234.97 rows=99999 width=16) -> Hash (cost=1931.92 rows=50331 width=8) -> Seq Scan on paints (cost=1931.92 rows=50331 width=8) B2) Primary Key on painter.id Hash Join (cost=12269.78 rows=60014 width=24) -> Seq Scan on painter (cost=4234.97 rows=99999 width=16) -> Hash (cost=1931.92 rows=50331 width=8) -> Seq Scan on paints (cost=1931.92 rows=50331 width=8) C1) BTree index on painter.id and Btree on paints.id1 Hash Join (cost=12269.78 rows=60014 width=24) -> Seq Scan on painter (cost=4234.97 rows=99999 width=16) -> Hash (cost=1931.92 rows=50331 width=8) -> Seq Scan on paints (cost=1931.92 rows=50331 width=8) C2) Primary Key on painter.id and Btree on paints.id1 Hash Join (cost=12269.78 rows=60014 width=24) -> Seq Scan on painter (cost=4234.97 rows=99999 width=16) -> Hash (cost=1931.92 rows=50331 width=8) -> Seq Scan on paints (cost=1931.92 rows=50331 width=8) Regards, Sofia Alexaki