Thread: Large temporary file generated during query

I have a very large query that also touches quite a bit of data. It
generates a large temporary file (actually, several, because the total
size is about 4.5GB). I'm wondering which part of the query's plan
causes these files to be created. The query has the following sequence
of plan nodes when run through EXPLAIN:

HashAggregate -> Result -> Append -> [Seq Scan | Bitmap Heap Scan, ...]

Where there are many "Seq Scan" or "Bitmap Heap Scan" nodes being
generated, one for each partition of the partitioned table.

So, why are these files being created?

Also, I suppose this data on-disk can be kept in memory instead by increasing
work_mem to a suitable value?

Thanks,
-Ryan Kelly

On Fri, Dec 7, 2012 at 1:58 PM, Ryan Kelly <rpkelly22@gmail.com> wrote:
> I have a very large query that also touches quite a bit of data. It
> generates a large temporary file (actually, several, because the total
> size is about 4.5GB). I'm wondering which part of the query's plan
> causes these files to be created.

Doing an "explain (analyze, buffers)" should tell you that.

Cheers,

Jeff

On Fri, Dec 12/07/12, 2012 at 05:33:45PM -0800, Jeff Janes wrote:
> On Fri, Dec 7, 2012 at 1:58 PM, Ryan Kelly <rpkelly22@gmail.com> wrote:
> > I have a very large query that also touches quite a bit of data. It
> > generates a large temporary file (actually, several, because the total
> > size is about 4.5GB). I'm wondering which part of the query's plan
> > causes these files to be created.
>
> Doing an "explain (analyze, buffers)" should tell you that.
Was hoping to avoid that because the query had not finished running
after 48 hours. I'll see if it finishes in a reasonable amount time with
extra work_mem.

>
> Cheers,
>
> Jeff

-Ryan Kelly