Re: Partitions and work_mem?

On Wed, Oct 15, 2014 at 10:10 AM, Josh Berkus <josh(at)agliodbs(dot)com> wrote:

> On 10/14/2014 10:08 AM, Dave Johansen 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?
>
> In theory, this could happen. In practice, based on tests I did at Sun
> with DBT3 and 8.3, no backend ever used more than 3X work_mem. This is
> partly because the level of parallelism in postgres is extremely
> limited, so we can't actually sort 8 partitions at the same time.
>

Thanks for the feedback. That's very helpful.

> BTW, 8.4 is EOL. Maybe time to upgrade?
>

RHEL 6 isn't EOLed and we're working on moving to RHEL 7 but it's a slow
process that will probably take quite a bit of time, if it ever happens.