On Sat, Jun 26, 2010 at 11:59 AM, Martijn van Oosterhout <klep...@svana.org> wrote: > On Sat, Jun 26, 2010 at 11:37:16AM -0400, Robert Haas wrote: >> On Sat, Jun 26, 2010 at 11:03 AM, Martijn van Oosterhout >> > (It doesn't help in situations where you can't accurately predict >> > memory usage, like hash tables.) >> >> Not sure what you mean by this part. We already predict how much >> memory a hash table will use. > > By this I mean where the memory usage of the HashAggregate depends on > how many groups there are, and it's sometimes very difficult to predict > that beforehand. Though maybe that got fixed.
Oh, I see. Well, yeah, it's possible the estimates aren't that good in that case. I think it's a fairly rare query that has more than one aggregate in it, though, so you're probably OK as long as you're not TOO far off - where you can really use up a lot of memory, I think, is on a query that has lots of sorts or hash joins. > Another issue is cached plans. Say there is increased memory pressure, > at what point do you start replanning existing plans? The obvious thing to do would be to send an invalidation message whenever you changed the system-wide cost value for use of memory, but maybe if you're changing it in small increments you'd want to be a bit more selective. > While this does have the advantage of being relatively simple to > implement, I think it would be a bitch to tune... I'm not sure. What does seem clear is that it's fundamentally at odds with the "admission control" approach Kevin is advocating. When you start to run short on a resource (perhaps memory), you have to decide between (a) waiting for memory to become available and (b) switching to a more memory-efficient plan. The danger of (b) is that using less memory probably means using more of some other resource, like CPU or disk, and now you've just switched around which release you're overloading - but on the other hand, if the difference in CPU/disk is small and the memory savings is large, maybe it makes sense. Perhaps in the end we'll find we need both capabilities. I can't help feeling like some good instrumentation would be helpful in answering some of these questions, although I don't know where to put it. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
