Re: [HACKERS] Another index "buglet"?

After the VACUUM ANALYZE:

Straight start up:
Hash Join  (cost=9994.31 rows=2740488 width=36) ->  Seq Scan on url  (cost=3368.58 rows=37866 width=20) ->  Hash
(cost=3368.58rows=37866 width=16)       ->  Seq Scan on url url2  (cost=3368.58 rows=37866 width=16)
 

788u 0.327s 0:03.89 28.2%     104+14868k 0+179io 0pf+0w


Forbid merge:
Hash Join  (cost=9994.31 rows=2740488 width=36) ->  Seq Scan on url  (cost=3368.58 rows=37866 width=20) ->  Hash
(cost=3368.58rows=37866 width=16)       ->  Seq Scan on url url2  (cost=3368.58 rows=37866 width=16)
 

0.900u 0.217s 0:04.19 26.4%     103+14638k 0+175io 0pf+0w


Forbid Hash:
Merge Join  (cost=11188.76 rows=2740488 width=36) ->  Index Scan using url_pkey on url url2  (cost=4347.30 rows=37866
width=16)->  Index Scan using url_referrer on url  (cost=4342.30 rows=37866 width=20)
 

0.897u 0.210s 0:03.19 34.4%     106+15120k 0+179io 0pf+0w

On Sat, 8 Jan 2000, Tom Lane wrote:

> The Hermit Hacker <scrappy@hub.org> writes:
> > SELECT url.status,url2.url,url.url 
> >   FROM url,url url2 
> >  WHERE url.referrer=url2.rec_id;
> 
> > There is an index on rec_id and one on referrer ... shouldn't one of the
> > be used?
> 
> Not necessarily --- hash join is a perfectly respectable alternative
> choice.  I'd expect to see either a hash or a merge join here (the
> merge *would* use both indexes).
> 
> Now it could be that the optimizer is misestimating the relative costs
> of merge and hash join.  If you're interested in checking that, do
> this (*after* running VACUUM ANALYZE, ahem):
> 
> 1. Start psql with environment variable PGOPTIONS="-fh" (forbid hash).
>    Do the EXPLAIN --- it'll probably give a mergejoin plan now.  Note
>    the estimated total cost.  Run the query itself, and note the runtime.
> 
> 2. Start psql with environment variable PGOPTIONS="-fm" (forbid merge),
>    and repeat the experiment to get the estimated cost and actual time
>    for the hash join.
> 
> I'd be interested to know what you find out.  I'm in the middle of
> rejiggering the optimizer's cost estimates right now, so more data
> points would be helpful.
> 
>             regards, tom lane
> 

Marc G. Fournier                   ICQ#7615664               IRC Nick: Scrappy
Systems Administrator @ hub.org 
primary: scrappy@hub.org           secondary: scrappy@{freebsd|postgresql}.org