Re: Query sometimes takes down server - Mailing list pgsql-general

Jeff Davis wrote:

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. 

The schema is not auto generated.  It evolved as I created my inventory system.
It is relatively easy for humans to understand.  Or at least for me since I wrote it. 

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). 

I am familiar with with Global Optimization.  I was part of my research for my masters degree.

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.
 

I raised the geqo_effort to 10 and this made this happen my less frequently, but still fairly often.
Thanks for the advice.  I will post my results if I achieve improvement.

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. 

I think I am going to write a script an run the query enough times for me to see some statistics on how my tuning of the parameters work.
Would anyone have such a script already made?

Maybe I should use a genetic algorithm to analyze all the possible combinations of GEQO parameters for may case. :)
But, realistically I am slammed with work and while this is very interesting to me I will have to keep cranking out new features to keep the clients happy.
Hopefully, I can find a a better set of parameters through trial and error.

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. 

Making them more rare is probably good enough for now.  Thank you very much for the advice.

Regards,Jeff Davis