Re: Nested loops overpriced

Peter Eisentraut <peter_e(at)gmx(dot)net> 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