Re: trouble with (lack of) indexing

On Thu, 9 May 2002, Tom Lane wrote:

> =?iso-8859-1?Q?S=F8ren?= Boll Overgaard <postgres@fork.dk> writes:
> >  explain SELECT ht.id,hq.ip,hq.id FROM
> >  hostsqueue as hq,hoststests as ht WHERE  ht.hostsqueue_id=hq.id;
> > NOTICE:  QUERY PLAN:
>
> > Hash Join  (cost=776.05..2904.36 rows=84 width=24)
> >   ->  Seq Scan on hoststests ht  (cost=0.00..2126.84 rows=84 width=8)
> >   ->  Hash  (cost=776.04..776.04 rows=4 width=16)
> >         ->  Seq Scan on hostsqueue hq  (cost=0.00..776.04 rows=4 width=16)
>
>
> If there's only 84 rows it's hardly going to matter which plan we choose
> ;-).  Please show us the results from the production database, not the
> toy-sized tables.

The original post had it, but presumably not from the most recent analyze:

[quote]

This however is on the production database:
--------------8<-----------

=> explain SELECT ht.id,
->                      hq.ip,
->                      hq.id
->               FROM   hostsqueue as hq,
->                      hoststests as ht
->               WHERE  ht.hostsqueue_id=hq.id;
NOTICE:  QUERY PLAN:

Merge Join  (cost=134514.31..136541.15 rows=129756 width=44)
  ->  Sort  (cost=76196.94..76196.94 rows=32200 width=36)
        ->  Seq Scan on hostsqueue hq  (cost=0.00..73786.00 rows=32200 width=36)
  ->  Sort  (cost=58317.37..58317.37 rows=129756 width=8)
        ->  Seq Scan on hoststests ht  (cost=0.00..47297.56 rows=129756 width=8)

EXPLAIN
=>
[/end quote]

It's quote a result set. I wouldn't want to be paging/scrolling through that
lot.


> If you have 7.2 then EXPLAIN ANALYZE is much more useful to show than
> plain EXPLAIN.  Also, you could try doing "SET enable_seqscan = off"
> and see how the plan changes.

Would it be worth changing the explain output to include the stats., or a
summary of them, used by the plan? Does the 10% rule still apply? If so it
might be a nice addition to show the total number of rows expected in the table
so that a quick 'eyeball' can confirm that is why a seqscan is being
performed. Or am I just barking up a dead horse here since I'm now thinking it
might be nice to include information about potential indexes and at what row
count from them it's decided a seqscan is better.


--
Nigel J. Andrews
Director

---
Logictree Systems Limited
Computer Consultants