Re: trouble with (lack of) indexing

On Thu, May 09, 2002 at 07:38:06PM -0400, 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.

Sorry, my bad.
Here is the real stuff:

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=75331.89..127823.94 rows=160293 width=24)
  ->  Seq Scan on hoststests ht  (cost=0.00..49686.93 rows=160293 width=8)
  ->  Hash  (cost=75243.51..75243.51 rows=35351 width=16)
        ->  Seq Scan on hostsqueue hq  (cost=0.00..75243.51 rows=35351 width=16)

EXPLAIN




>
> 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.

After changing the enable_seqscan, the plan looks like this:

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=0.00..772324.63 rows=160293 width=24)
  ->  Index Scan using hostsqueue_pkey on hostsqueue hq  (cost=0.00..137603.50 rows=35351 width=16)
  ->  Index Scan using idx_htsts_hq on hoststests ht  (cost=0.00..632228.35 rows=160293 width=8)

EXPLAIN


As far as I can tell, that is excactly what we want. I will let the guys who
usually mess with the database have a go at it.

Thanks for the input so far, I really appreciate it.

--
Søren O.                                       ,''`.
                                              : :' :
public key: finger boll <at> db.debian.org    `. `'
                                                `-