On Sun, Jan 30, 2005 at 01:05:15PM -0500, Tom Lane wrote: > "Jim C. Nasby" <decibel@xxxxxxxxxxx> 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@xxxxxxxxxxx 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?" ---------------------------(end of broadcast)--------------------------- TIP 8: explain analyze is your friend
