Re: General performance/load issue

On 26.11.2011 19:08, Tom Lane wrote:
> Tomas Vondra <tv(at)fuzzy(dot)cz> writes:
>> Anyway the output is a bit strange. It's writing a lot of temp files
>> that are significantly smaller (about 3MB) than work_mem (128MB).
>
> The most obvious guess about what's doing that is a hash join that has
> a drastic overestimate of how many rows it has to hash, so that it's
> batching the join unnecessarily. AFAIR, sorts don't spill to disk until
> they've actually reached work_mem, but hash join works differently.

OK, so it probably is not sorting, probably. What about other nodes
using hash table - aggregates etc.? Would those spill to the disk in
case of overestimation?

Gaetan did not post the whole query, but it starts like this:

SELECT COUNT(*) FROM (SELECT DISTINCT "table"."id" AS "id",
"table"."flux_id" AS "flux_id", "table"."locale_id" AS "locale_id",
...

so it obviously uses a DISTINCT clause, probably evaluated using a hash
aggregate. And this is allegedly one of the queries that write a lot.

Tomas