Re: pgsql_tmp consuming all inodes

"Nestor A. Diaz" <nestor(at)tiendalinux(dot)com> writes:
> On 11/21/2014 10:15 AM, Tom Lane wrote:
>> Could we see what EXPLAIN says about that?

> look at this query (this use partitioning with table inheritance):

I asked for an EXPLAIN of the problematic query, not something weakly
related to it :-(. However, if these rowcount estimates are anywhere close
to reality, it's not exactly surprising that you're seeing huge amounts
of temporary storage:

> -> Hash Join (cost=34247633.75..5386910348.86 rows=474058400025 width=0)
> ...
> -> Hash (cost=16399273.75..16399273.75 rows=1087900000 width=4)

The hash join is estimated to need to put a billion rows into its hash
table and then to return nearly 500 billion rows --- which, in your
original query, would need to be sorted. Even if the hash table didn't
spill to disk, the sort certainly would, unless these estimates are off
by a lot of orders of magnitude.

So as was asked upthread, just how much data are you expecting this
query to return? Are you sure you've got the join conditions right?
It's evidently the joins to "dev" and "bev" that the planner thinks
are going to cause huge multiplications of the number of rows, so
perhaps those are the ones to look at closely.

regards, tom lane