Re: Choice of bitmap scan over index scan

On Mon, Jan 11, 2010 at 2:41 PM, Jeremy Harris  wrote:
> On 01/11/2010 02:53 AM, Robert Haas wrote:
>>
>> On Sun, Jan 10, 2010 at 9:04 AM, Jeremy Harris  wrote:
>>>
>>> Needing to use an external (on-disk) sort method, when taking
>>> only 90MB, looks odd.
>
> [...]
>>
>> Well, you'd need to have work_mem>  90 MB for that not to happen, and
>> very few people can afford to set that setting that high.  If you have
>> a query with three or four sorts using 90 MB a piece, and five or ten
>> users running them, you can quickly kill the box...
>
> Oh.  That's, um, a real pity given the cost of going external.  Any hope
> of a more dynamic allocation of memory resource in the future?
> Within a single query plan, the number of sorts is known; how about
> a sort-mem limit per query rather than per sort (as a first step)?

Unfortunately, it's not the case that the number of sorts is known -
the amount of memory available to the sort affects its cost, so if we
knew we were going to have more or less memory it would affect whether
we chose the plan involving the sort in the first place.

My previous musings on this topic are here:

http://archives.postgresql.org/pgsql-hackers/2009-10/msg00125.php

...Robert