"Nestor A. Diaz" <nestor@xxxxxxxxxxxxxxx> 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
