Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> "Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov> writes:
>> With all the optimizer options on, and the from_collapse_limit and
>> join_collapse_limit values both set to 100, run an EXPLAIN (no
>> ANALYZE) on your big problem query. Let us know how long the
>> EXPLAIN runs. If it gets any errors, copy and paste all available
>> information. (General descriptions aren't likely to get us very
>> far.) Since EXPLAIN without ANALYZE only *plans* the query, but
>> doesn't run it, it should not take long to do this.
>
> One issue here is that with the collapse limits cranked up to more
> than geqo_threshold, he's going to be coping with GEQO's partially-
> random plan selection; so whatever he reports might or might not be
> especially reflective of day-to-day results. I'm tempted to ask
> that he also push up geqo_threshold.
In an earlier post[1] he said that he had geqo turned off. It does
pay to be explicit, though; I'd hate to assume it's of if he's been
changing things.
Alberto, please ensure that you still have geqo off when you run the
test I suggested. Also, I see that I didn't explicitly say that you
should send the ANALYZE output, but that's what would be helpful.
> It's possible that that *will* send the planning time to the moon;
> but it would certainly be worth trying, to find out what plan is
> produced.
Agreed. What plan is produced, and how long that takes. (And whether
he gets an out of memory error.) I figured it was best to get a clear
answer to those before moving on....
-Kevin
[1]
http://archives.postgresql.org/pgsql-performance/2009-06/msg00186.php