Re: multiple runs of the same query cause out of memory - WAS [Re: capturing/viewing sort_mem utilization on a per query basis]

On Thu, 24 Feb 2005 22:46:02 -0500, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Lonni J Friedman <netllama(at)gmail(dot)com> writes:
> > Oh, also, do you see a time difference in running this query with &
> > without the genetic optimizer?
>
> Yeah, but that's no surprise.
>
> The reason I couldn't replicate your problem was I was trying 8.0.
> What I've found so far is that the 8.0 GEQO consistently finds a good
> plan while 7.4 usually finds a bad to awful plan :-(. In about 30 tries
> with 7.4, I got two OOMs and two that I gave up on after upwards of 20
> minutes runtime. The runtimes of the rest were all over the map ---
> up to 30 times slower than the plan found with geqo off.
>
> In about 200 tries, 8.0 all but once found a plan no worse than 50%
> slower than what it found with geqo off; and even the outlier was only
> 3x slower than the best plan.
>
> So at least on this example, the GEQO tweaking we did for 8.0 really
> paid off.
>
> As long as you're on 7.4, disabling GEQO may be a good answer. Rather
> than just setting geqo = off, I'd advise bumping geqo_threshold up a
> notch or two. That will allow this query to be planned by the regular
> planner, without buying into doing impossibly complex queries that way.

Thanks for your help on this. Immediately, we've disabled the genetic
optimizer, and we'll tinker with increaseing the threashold parameter
to see if that produces more stability.

--
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
L. Friedman netllama(at)gmail(dot)com
LlamaLand http://netllama.linux-sxs.org