Re: [HACKERS] Maximum statistics target

[Decibel!]

On Mar 10, 2008, at 1:26 PM, Peter Eisentraut wrote:

Am Montag, 10. März 2008 schrieb Gregory Stark:

It's not possible to believe that you'd not notice O(N^2) behavior for N approaching 800000 ;-). Perhaps your join columns were unique keys, and

thus didn't have any most-common-values?

instead on the actual size of the arrays. Ie, allow people to specify

warning them when that happens).

We could remove the hard limit on statistics target and impose the limit larger sample sizes and discard unreasonably large excess data (possibly

maintenance_work_mem could provide additional safety.  If we could get

bound for the statistics targets, and it would be controllable by the
administrator.

I have run some more useful tests now with more distinct values. The planning times do increase, but this is not the primary worry. If you want to spend 20 seconds of planning to speed up your query by 40 seconds, this could surely be a win in some scenarios, and not a catastrophic loss if not. The practical problems lie with memory usage in ANALYZE, in two ways. First, at some point it will try to construct pg_statistic rows that don't fit into the 1GB limit, as mentioned upthread. You get a funny error message and it aborts. This is fixable with some cosmetics. Second, ANALYZE appears to temporarily leak memory (it probably doesn't bother to free things along the way, as most of the code does), and so some not so large statistics targets (say, 40000) can get your system swapping like crazy. A crafty user could probably kill the system that way, perhaps even with the restricted settings we have now. I haven't inspected the code in detail yet, but I imagine a few pfree() calls and/or a counter that checks the current memory usage against ANALYZE under control, then I imagine this would provide a more natural upper

--
Decibel!, aka Jim C. Nasby, Database Architect  [EMAIL PROTECTED]
Give your computer some brain candy! www.distributed.net Team #1828

At some point I think it makes a lot more sense to just have VACUUM gather stats as it goes, rather than have ANALYZE generate a bunch of random IO. BTW, when it comes to the case of the OP, perhaps we can build enough intelligence for the system to understand when the stats follow some type of pattern (ie: a geometric distribution), and store the stats differently.

smime.p7s
Description: S/MIME cryptographic signature