Richard Huxton <dev@xxxxxxxxxxxx> writes:
> What happens to the plan if you SET enable_seqscan=false; first? It's
> presumably getting the row-estimate right, so unless there's terrible
> correlation on "base" in the files table I can only assume it's getting
> the cost estimates horribly wrong.
I think you'll find that the results suck ;-). It looks to me that the
planner is making exactly the right choice here. The only plausible
alternative is a nestloop with inner indexscan on "files", which would
imply 176160 separate index probes into "files", which is unlikely to
win compared to one seqscan. (In the aggregate the index probes are
likely to end up touching every page of "files" anyway --- you would
need a much larger files table before this stopped being true.)
If you want to compare the nestloop plan to test this theory, turning
off enable_hashjoin and (if necessary) enable_mergejoin would be the
better way to get it. But let's see EXPLAIN ANALYZE results for both
cases, not just EXPLAIN.
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
