From: | Olwen Williams <olwen(at)ihug(dot)co(dot)nz> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | Search optimisation |
Date: | 1999-12-15 00:26:44 |
Message-ID: | 3856E044.21391F03@ihug.co.nz |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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?
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 1999-12-15 06:57:07 | Re: [SQL] Search optimisation |
Previous Message | De Moudt Walter | 1999-12-14 20:21:26 | Re: [SQL] SQL'92 web resources |