Re: Partitions and work_mem?

On Tue, Oct 14, 2014 at 10:08 AM, Dave Johansen <davejohansen(at)gmail(dot)com>
wrote:

> I'm running Postgres 8.4 on RHEL 6 64-bit and I had a question about how
> work_mem and partitions interact.
>
> https://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server#work_mem
> The above wiki states that "if a query involves doing merge sorts of 8
> tables, that requires 8 times work_mem." If I have a table that is
> partitioned does each partition count as a "table" and get its on work_mem?
>
> For example, say I have the following table partitioned by the time column:
> CREATE TABLE values (time TIMESTAMP, value INTEGER);
> If I do the following query will it require 1 work_mem or N work_mem's
> (where N is the number of partitions)?
> SELECT * FROM values ORDER BY time;
>

The specific query you show should do the append first and then the sort on
the result, and so would only use 1 work_mem.

However, other queries could cause it to use one (or more) sorts per
partition, for example a self-join which it decides to run as a sort-merge
join.

Cheers,

Jeff