Re: Partitions and work_mem?

 

 

From: Dave Johansen [mailto:davejohansen@gmail.com]
Sent: Wednesday, October 15, 2014 4:20 PM
To: Igor Neyman
Cc: Josh Berkus; pgsql-performance
Subject: Re: [PERFORM] Partitions and work_mem?

 

On Wed, Oct 15, 2014 at 1:08 PM, Igor Neyman <ineyman@perceptron.com> wrote:

 

 

From: pgsql-performance-owner@postgresql.org [mailto:pgsql-performance-owner@postgresql.org] On Behalf Of Dave Johansen
Sent: Wednesday, October 15, 2014 4:05 PM
To: Josh Berkus
Cc: pgsql-performance
Subject: Re: [PERFORM] Partitions and work_mem?

 

On Wed, Oct 15, 2014 at 10:10 AM, Josh Berkus <josh@agliodbs.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.

 

 

Postgres 8.4 is EOL (RHEL).

 

Sorry I don't understand what you mean by that. My understanding is that RedHat maintains fixes for security and other major issues for packages that have been EOLed. Are you implying that that's not the case? Or something else?

 

I don’t think that RedHat can maintain Postgres version which was EOLed.

Postgres 8.4 is not supported by PostgreSQL community.