But isn't the problem when the planner screws up and not the sortmem setting?
There was my case where the 7.4 planner estimated 1500 distinct rows when there were actually 1391110. On 7.3.4 it used about 4.4MB. Whereas 7.4 definitely used more than 400MB for the same query ) - I had to kill postgresql - didn't wait for it to use more. That's a lot more than 200%. Maybe 3x sort_mem is too low, but at least by default keep it below server RAM/number of backends or something like that.
Even if the planner has improved a lot if cases like that still occur from time to time it'll be a lot better for stability/availability if there's a limit.
Doubt if I still have the same data to test on 8.0.
Link.
At 12:35 AM 12/7/2004 -0500, Tom Lane wrote:
Neil Conway <[EMAIL PROTECTED]> writes: > As a quick hack, what about throwing away the constructed hash table and > switching to hashing for sorting if we exceed sort_mem by a significant > factor? (say, 200%) We might also want to print a warning message to the > logs.
If I thought that a 200% error in memory usage were cause for a Chinese fire drill, then I'd say "yeah, let's do that". The problem is that the place where performance actually goes into the toilet is normally an order of magnitude or two above the nominal sort_mem setting (for obvious reasons: admins can't afford to push the envelope on sort_mem because of the various unpredictable multiples that may apply). So switching to a hugely more expensive implementation as soon as we exceed some arbitrary limit is likely to be a net loss not a win.
If you can think of a spill methodology that has a gentle degradation curve, then I'm all for that. But I doubt there are any quick-hack improvements to be had here.
regards, tom lane
---------------------------(end of broadcast)--------------------------- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
