Re: changing sort_mem on the fly?

On Sun, Jan 30, 2005 at 01:05:15PM -0500, Tom Lane wrote:
> "Jim C. Nasby" <decibel(at)decibel(dot)org> writes:
> > On Sun, Jan 30, 2005 at 08:50:49PM +1100, Neil Conway wrote:
> >> Do you have any thoughts on how to improve it?
>
> > See http://archives.postgresql.org/pgsql-general/2003-06/msg01072.php
> > and http://archives.postgresql.org/pgsql-general/2003-06/msg01089.php
>
> All of this falls down on twin problems: (1) we have no portable way to
> find out how much RAM is really available, and (2) the planner has to
> make a cost estimate for the sort in advance of actual runtime --- which
> requires making some assumption about the value of sort_mem that will be
> used.

As I said, I'm sure there's better minds who can come up with better
ideas than I. :) I personally think this is important enough to warrant
a TODO so those minds can think about it at some point, but that's just
my opinion.

As for your two points: if you mean how much RAM is available for
sorting, I assumed there would be a GUC for that, although
effective_cache_size might be used.

I thought that the planner did estimate sort size when it called the
sort code, but it's been a long time since I looked at it and it was
somewhat over my head anyway. Since the planner knows how many rows will
be going into the sort and how wide they are, ISTM it should be able to
estimate how much memory will be needed. Even if it can't, I think you
could still get by with just keeping track of how much memory a sort is
using when it asks for more memory. At some point it would hit a
threshold and would then spill to disk.

BTW, if it would be useful, I could test sort speeds for sorts that
spill to disk with different sort_mem settings. That would help answer
the question of how much it would hurt for a sort that spills to disk to
give back some of it's memory so that other sort operations wouldn't
spill to disk.
--
Jim C. Nasby, Database Consultant decibel(at)decibel(dot)org
Give your computer some brain candy! www.distributed.net Team #1828

Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"