Henrik Zagerholm <henke@xxxxxx> writes: > At what point does the planner choose seq scans? When it thinks it's cheaper than the other way. There's no hard and fast answer. The immediate problem you've got is that the estimated size of the tbl_file/tbl_filetype_suffix join is off by a factor of almost 20 (8061 vs 486). The plan that you think would be faster involves an inner indexscan on the larger table for each result row from that join, and therefore this error translates directly to a 20x overestimate of its cost, and therefore the planner avoids that in favor of a hash join that indeed is more efficient when there are lots of rows to be joined. It may well be that you also need to adjust random_page_cost and/or effective_cache_size so that the planner's estimates of indexscan vs seqscan costs are more in line with reality on your machine. But it's a capital error to tinker with those numbers on the basis of an example where the rowcount estimates are so far off. (Actually I'd not advise changing them on the basis of *any* single test case, you need to look at average behaviors. Get the count estimates fixed first and then see where you are.) It's also not impossible that the planner is right and the seqscan is better than a lot of index probes ... regards, tom lane ---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend
