sibo_oskam wrote:

>
> I use the latest version of firebird 1.5.2.4731.
>
> Select Count(*) From Employee E Inner Join Employer R On
> (E.EmployerId=R.Id) Where R.CityId In (10,20,30)

I tried this and got different results... My employees table as
25,000,000 records. The employers table has 5000. I believe I have the
same indexes as you - one on employer_id from employees and one on city
from employers.

SQL> select count (*) from employees x join employers y on x.employer_id
CON> = y.id where y.city in (10, 20, 30);

PLAN JOIN (Y INDEX (EMPLOYERS2, EMPLOYERS2, EMPLOYERS2), X INDEX
(EMPLOYEE2))

COUNT
============

750000

Elapsed time= 3.76 sec
Buffers = 2048
Reads = 13200


I wonder why you're seeing different results. For what it's worth, I
reversed the join order and got exactly the same plan - same reads,
writes, fetches, but it ran in 2.89 seconds - operating system caching,
I suspect.

> I tried to rewrite it as:
> Select Count(*) From Employee E Where EmployerId In (Select Id from
> Employer R Where R.CityId=(10,20,30))
>
> It is just as slow and it don't uses the index.

That variant is really really slow. I looked at the plan, thought about
reading 25 million records 50 times, and killed the query.

SQL> select count (*) from employees e where e.employer_id in
CON> (select r.id from employers r where r.city in
CON> (10, 20, 30));

PLAN (R INDEX (EMPLOYERS2, EMPLOYERS2, EMPLOYERS2))
PLAN (E NATURAL)


Regards,


Ann