On Fri, 2009-01-16 at 08:43 -0600, Jason Long wrote:
> The numbers in the table names are due to hibernate generating the
> query.
Well, that's what auto-generated schemas and queries do, I guess.
> Now we are getting somewhere.
> Someone suggested tweaking the genetic algorithm parameters.
> Has anyone else had to do this and what results did you acheive?
> Can someone offer me some detailed advice on tweaking these
> parameters?
There are a lot of tables, so no matter what you do will require GEQO
(the genetic algorithm I was talking about).
The fact that some of the plans are fast is good news: it means that
it's possible to execute the query quickly.
The other good news is that the slower plans are, indeed, estimated to
be slower in the examples you provided (not by exactly proportional
amounts, but it's still a good sign). If the estimations are so far off
that they are basically random, GEQO won't help much; but in your case
they look surprisingly good.
I would try increasing geqo_effort, and tweaking geqo_pool_size and
geqo_generations (mostly try increasing these last two, but smaller
values might be useful), and tweak geqo_selection_bias randomly between
1.5 and 2.
See useful ranges of the parameters here:
http://www.postgresql.org/docs/8.3/static/runtime-config-query.html
When you start to get stable execution times (make sure you don't just
get lucky once), keep the values you're using. Post to the list with
your results.
You may be able to fix some of your queries (like this one), but I
suspect this will just make the problem more rare. When you come up with
some new query later, I think the problem will come back. The solution
is really to have a more reasonable schema, something that PostgreSQL
(and humans) can understand well enough to optimize.
Regards,
Jeff Davis