Received: from smtp1.ihug.co.nz (tk1.ihug.co.nz [203.29.160.13]) by hub.org (8.9.3/8.9.3) with ESMTP id TAA12361 for ; Tue, 14 Dec 1999 19:26:42 -0500 (EST) (envelope-from olwen@ihug.co.nz) Received: from ihug.co.nz (p43-max4.wlg.ihug.co.nz [209.78.48.235]) by smtp1.ihug.co.nz (8.9.3/8.9.3/Debian/GNU) with ESMTP id NAA10458 for ; Wed, 15 Dec 1999 13:26:39 +1300 Message-ID: <3856E044.21391F03@ihug.co.nz> Date: Wed, 15 Dec 1999 13:26:44 +1300 From: Olwen Williams X-Mailer: Mozilla 4.7 [en]C-FREESERVE (Win98; I) X-Accept-Language: en,pdf MIME-Version: 1.0 To: pgsql-sql@postgresql.org Subject: Search optimisation Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit I'm new to SQL databases although I've worked a lifetime on PICK type systems. I'm having trouble gettting selects to work well. I have a database with a number of tables. I'm having a number of problems one is this: This query runs very quickly and returns one row: select * from biblioitems where isbn='031051911X'; explanation: Index Scan using isbnidx on biblioitems (cost=203.35 rows=2988 width=102) This is fast: select * from biblioitems,items,biblio where biblioitems.biblionumber ='109' and biblioitems.biblionumber = items.biblionumber and biblio.biblionumber = biblioitems.biblionumber explanation: Nested Loop (cost=54.91 rows=657 width=340) -> Nested Loop (cost=4.08 rows=2 width=167) -> Index Scan using bibitbnoidx on biblioitems (cost=2.03 rows=1 width=102) -> Index Scan using bibnumidx on biblio (cost=2.05 rows=59945 width=65) -> Index Scan using bibnumitem on items (cost=25.42 rows=73185 width=173) This one is slow: select * from biblioitems,items,biblio where isbn='031051911X' and biblioitems.biblionumber = items.biblionumber and biblio.biblionumber = biblioitems.biblionumber; explanation: Hash Join (cost=11830.17 rows=981357 width=340) -> Seq Scan on items (cost=4158.10 rows=73185 width=173) -> Hash (cost=5091.33 rows=2989 width=167) -> Hash Join (cost=5091.33 rows=2989 width=167) -> Seq Scan on biblio (cost=2767.19 rows=59945 width=65) -> Hash (cost=203.35 rows=2988 width=102) -> Index Scan using isbnidx on biblioitems (cost=203.35 rows=2988 width=102) How can I make this query use the indexes?