At 07:48 PM 10/01/2005 +0000, you wrote:

>I have big database (about 50 tables). I run huge select on several
>tables. That select has 3 subselects. Sometimes (I cannot reproduce)
>this select is running very slow (about 100x slower than normal), all
>that time about 1 hour server cpu stays loaded 100% (normaly this
>query runs about 10s), statistics shows that were fetched milions of
>records (in fact where sould be just about 1000).

That sounds like a fault in the query itself. Subqueries generally should
be avoided in huge queries and replaced with left joins; or improved with
astute indexing if they are unavoidable. There are a lot of interdependent
factors influencing this, though, and we need some proper information from you.

>The same thing
>happening from console and from my app. Sometimes if I reboot server
>(Linux 1.5.2 FB) select starts to run normaly.
>If I use COALESCE(subselect, null) or even parameter in my subselect
>this slows down about 30%.
>
>This could be FB bug,

There is no evidence that it could be. Thousands of people are using
v.1.5.2 without this problem. That said, a certain optimization done in
v.1.5.2 has been shown to have a poor effect on some unusual queries. So
it's essential for us to know exactly what your query is trying to do.

>I can send my database and app

No thanks. If it turns out that something weird appears to be going on,
you would be asked to design and submit a reproducible test case in
firebird-devel, that can be tested with generic tools.

>or at least that query if anyone can help me.

Yes, post the query, along with the exact plans generated by the engine;
and also tell us about the keys and indexes involved.

./heLen