ERROR: could not write block 37583345 of temporary file: No space left on device

Question

I want an output like this:

obid     | sid_count
1        |  3
2        |  2
3        |  4

The obid is on custdata table and sid_count is get from identifier table.

The sample data is:

custdata
obid
1
2
3

identifier
obid | type
1    | SID
1    | SID
1    | XID
1    | SID
2    | SID
2    | SID
3    | SID
3    | SID
3    | XID
3    | SID
3    | SID

I try to run this query:

select custdata.obid,
count (identifier.obid) filter (where identifier.type = 'SID') as sid_count
from myschema.custdata, myschema.identifier group by custdata.obid

it took about an hour but got an error:

[53100] ERROR: could not write block 37583345 of temporary file: No space left on device

The custdata is about 65 million records. The identifier is about 250 million records.

How to overcome this problem? Why the database need to write to disk? or do i need to rewrite my query? because i can't add more space to the disk.

Thanks.

Answer 1

The problem is that you inadvertently wrote a cross join:

from myschema.custdata, myschema.identifier

That is, each of the 250 million rows of the one table is joined with each of the 65 million rows of the other table, resulting in 16.25 quadrillion result rows. Your data directory does not seem to have room to cache the temporary required to complete the query, so you are running out of disk space there.

As a solution, add a join condition.

Take the opportunity and learn to never again write joins like this. Always use the standard syntax:

FROM a JOIN b ON <condition>

That way you cannot forget the join condition, unless you explicitly specify

FROM a CROSS JOIN b

which will be way more obvious.