Обсуждение: temporary memory configuration

hey folks, 

so suppose I have a query that in explain analyze 
Sort Method: external merge Disk: 218080kB

What param should I set to high up, to end up with that sort in memory, and also - will that memory will always be allocated per connection, (I am bit worried that having say 320MB of temporary memory space per connection will reduce number of possible connections). The query is not run quite often, but it would be nice to see it running faster, as it is a report type of query - and these ppl are usualy very unpatient. Currently it runs around 1-2 minutes with 1-6M rows to process join etc across 2-3 tables.

--
GJ

On Wed, Nov 5, 2008 at 8:09 AM, Grzegorz Jaśkiewicz <gryzman@gmail.com> wrote:
> hey folks,
>
> so suppose I have a query that in explain analyze
>
> Sort Method: external merge Disk: 218080kB
>
> What param should I set to high up, to end up with that sort in memory, and

work_mem

> also - will that memory will always be allocated per connection,

It is allocated PER SORT, up to the amount needed.  Note that you can
set work_mem for a given connection to override the default setting,
so you don't have to set workmem to 256M for everybody, but just this
one operation.  You can also set default work_mem per user and per
database.

> (I am bit
> worried that having say 320MB of temporary memory space per connection will
> reduce number of possible connections). The query is not run quite often,
> but it would be nice to see it running faster, as it is a report type of
> query - and these ppl are usualy very unpatient. Currently it runs around
> 1-2 minutes with 1-6M rows to process join etc across 2-3 tables.

Definitely look at setting it in this connection at run time then.