pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Olwen Williams <olwen@ihug.co.nz>
To: pgsql-sql@postgresql.org
Subject: Search optimisation
Date: Wed, 15 Dec 1999 13:26:44 +1300
Message-ID: <3856E044.21391F03@ihug.co.nz> (raw)

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?





view thread (2+ messages)  latest in thread

Message-ID: <3856E044.21391F03@ihug.co.nz>
Permalink:  ../3856E044.21391F03@ihug.co.nz/
Also on:    postgresql.org/message-id/3856E044.21391F03@ihug.co.nz

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: olwen@ihug.co.nz
  Subject: Re: Search optimisation
  In-Reply-To: <3856E044.21391F03@ihug.co.nz>
  References: <3856E044.21391F03@ihug.co.nz>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox