Re: [HACKERS] Another index "buglet"?

The Hermit Hacker <scrappy(at)hub(dot)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