Subject Re: Slow Query
Author elmarhaneke
--- In, "almaier" <almaier@y...> wrote:

As explained in the other answers this query does involve an enormous
amount of work (many Gigabytes of temporary data)

To solve the problem tryout to split the query:

Do make better Names I do rename the fields to:

SELECT F1_1,N1_2, f2_2,f2_3,SUM(f2_4),COUNT(*)
GROUP BY f1_1,n1_2,f2_2,f2_3

First part is to evaluate on Table 1:

SELECT F1_1,N1_2,COUNT(*) AS Count1
GROUP BY f1_1,n1_2

Second Part is to evaluate on Table 2:

SELECT f2_2,f2_3,SUM(f2_4) as Sum2,COUNT(*) as Count2
GROUP BY f2_2,f2_3

Third part is to merge both subqueries

select f1_1,b1_2,Count1*Count2,f2_2,f2_3,Sum2*count1
FROM Query1,Query2

If the "Group By" does significantly reduce the number of Rows, the
Query should run on reasonable speed that way.

I would assume that the Optimizer in PostgreSQL can evaluate the query
that way and the one on Firebird cannot do this.

It might be an option to Post this as an RFE. But, I would not expect
that someone will work on this as long as only that strange Query
would be optimized.