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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
