pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Search optimisation
2+ messages / 2 participants
[nested] [flat]

* Search optimisation
@ 1999-12-15 00:26 Olwen Williams <olwen@ihug.co.nz>
  1999-12-15 06:57 ` Re: [SQL] Search optimisation Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 2+ messages in thread

From: Olwen Williams @ 1999-12-15 00:26 UTC (permalink / raw)
  To: 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?





^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: [SQL] Search optimisation
  1999-12-15 00:26 Search optimisation Olwen Williams <olwen@ihug.co.nz>
@ 1999-12-15 06:57 ` Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 2+ messages in thread

From: Tom Lane @ 1999-12-15 06:57 UTC (permalink / raw)
  To: Olwen Williams <olwen@ihug.co.nz>; +Cc: pgsql-sql

Olwen Williams <olwen@ihug.co.nz> writes:
> [ why is query A so much faster than query B? ]

Hmm.  The system is correctly estimating that query A is much
faster than B:

> Nested Loop  (cost=54.91 rows=657 width=340)
                ^^^^^^^^^^
vs
>  Hash Join  (cost=11830.17 rows=981357 width=340)
               ^^^^^^^^^^^^^

so the question is why it thought this hashjoin plan was the best
available option for query B.  It looks to me like the problem has
to do with the estimated selectivities of the two restrictions on the
biblioitems table.  Query A recognizes that biblionumber='109' selects
just one item from biblioitems:

-> Index Scan using bibitbnoidx on biblioitems  (cost=2.03 rows=1 width=102)
                                                           ^^^^^^

so it produces a good plan, even though a doubly nested loop would
be an extremely horrible plan if there were a lot of rows involved.
(Actually, this is the first complaint about bad plans I've ever seen
in which doubly-nested-loop was the *right* choice ;-) ...)

Meanwhile, query B thinks that isbn='031051911X' is going to accept
several thousand rows from biblioitems:

-> Index Scan using isbnidx on biblioitems (cost=203.35 rows=2988 width=102)
                                                        ^^^^^^^^^

and because of that, it's choosing a plan that would be well adapted for
a lot of matching rows, yet is overkill (read inefficient) for a few
rows.

Now, I'm going to read between the lines and guess that the restriction
on isbn should yield only one biblioitems row; if this guess is wrong,
you can ignore all that follows.

If my guess is correct, then the blame lies with misestimation of the
selectivity of the isbn='XXX' clause.  You can double-check this by
doing 

	explain select * from biblioitems where isbn='031051911X';

and seeing whether the estimated row count is close to what you
actually get from doing this query.

The first question has to be "have you done a VACUUM ANALYZE lately?".
If not, the planner is working in the dark about the distribution of
isbn values, and you can't fairly blame it for guessing conservatively
that it's going to have to deal with a lot of rows.  However, if you
have done a VACUUM ANALYZE and still get a ridiculous estimate, then
that's a bug that I'd like to try to fix.  Please contact me off-list
and we can pursue the details of why this is happening.

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 2+ messages in thread


end of thread, other threads:[~1999-12-15 06:57 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1999-12-15 00:26 Search optimisation Olwen Williams <olwen@ihug.co.nz>
1999-12-15 06:57 ` Tom Lane <tgl@sss.pgh.pa.us>

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