Re: Convincing the query planner to play nice

Timothy Kane <tim(dot)kane(at)gmail(dot)com> writes:
> I seem to be having some grief with the 9.1.9 query planner favouring an index scan + merge join, over a sequential scan + hash join.

I believe the reason it's preferring the merge join plan is that it thinks
the executor will be able to terminate the merge join early as a
consequence of the range of join keys in "addresses" being only a fraction
of the range of join keys in "users". Notice that the total estimated
cost for the merge join is just a fraction of the full estimated cost of
the indexscan on "users"; the only way that's possible is if the indexscan
on "users" doesn't have to run through all of the table. Probably, the
range of join keys is wider than the planner thinks and so the merge join
can't terminate early. The fix therefore is to crank the stats target for
"addresses" up high enough that you get a reasonable value in pg_statistic
for the largest address_id value (look at the last histogram entry).

> Interestingly, on another instance of this same database running on postgres 8.3.8, the query planner correctly chooses the sequential scan method - having more sane cost estimates for the index scan method.

I think the 8.3 planner didn't take this effect into account. Or maybe it
did, but by chance the upper histogram value is closer to reality on the
older database.

regards, tom lane