Re: Hash or merge join instead of inner loop

"Jim C. Nasby" <jim@nasby.net> writes:
> I have a query that's cauing pgsql choose either a hash or merge join
> depending on how I mess with the stats variables, but it won't choose an
> nested loop, even though it's the fastest.

There's been some discussion about that before; you could check the
archives (now that they're up again ;-)).  I believe that the planner
overestimates the cost of a nestloop with inner indexscan, because it
costs the indexscans as though each one is an independent ab-initio
index search.  In reality, most of the upper btree levels will no doubt
stay in memory during such a query, and so this estimate charges many
more reads than really occur.  Fixing this is on the todo list, but no
one's got to it yet.  (It's not clear to me how to put the consideration
into the planner's cost algorithms in a clean way.)

            regards, tom lane