Peter Eisentraut <peter_e@xxxxxxx> writes: >> Are you using any nondefault planner settings? > random_page_cost = 3 > effective_cache_size = 384MB >> How big are these tables, anyway? > email 35 MB > email_header 421 MB > mime_part 37 MB Hmmm ... I see at least part of the problem, which is that email_header is joined twice in this query, which means that it's counted twice in figuring the total volume of pages competing for cache space. So the thing thinks cache space is oversubscribed nearly 3X when in reality the database is fully cached. I remember having dithered about whether to try to avoid counting the same physical relation more than once in total_table_pages, but this example certainly suggests that we shouldn't. Meanwhile, do the estimates get better if you set effective_cache_size to 1GB or so? To return to your original comment: if you're trying to model a situation with a fully cached database, I think it's sensible to set random_page_cost = seq_page_cost = 0.1 or so. You had mentioned having to decrease them to 0.02, which seems unreasonably small to me too, but maybe with the larger effective_cache_size you won't have to go that far. regards, tom lane
