> thanks for your reponse,
>
>> You haven't mentioned your used Firebird server version, but with 2.5 I
>> get the following execution plan "out-of-the box".
>>
>> PLAN JOIN (A INDEX (A_IDX1), B INDEX (B_IDX1, B_IDX3))
>>
>> And the query is executed in < 50ms returning 0 rows.
>>
>
> i use the same server on windows 7 64 with the 32 bit version.
> i obtain the same plan if i don't recompute index.
>
>>
>> But after recomputing index statistics, I get the same selectivity as
>> stated above and your mentioned execution plan as well:
>>
>> PLAN JOIN (A INDEX (A_IDX1), B INDEX (B_IDX1))
>>
>> but again, the query is executed in < 50ms returning 0 rows.
>>
>
> ok it retruns 0 rows because in my request i use date (14.08.2012 07:20:00) that i have generated this morning with the stored proc populate_a_b ('NOW') and for you the date should be different.
>
> if you change the date you could see my problem in the number of read in table B

Changed the date/time interval to another 1 hour window with data.

The query executes in ~100ms with an execution plan mentioned above.
Executing the query in context of a fetch all operation, I get 60
indexed reads on table A and 30000 indexed reads on B.

As the distribution for REFWROD via:

select refwrod, count(*) from b group by refwrod

shows the following:

REFWROD COUNT
-------------------------------------------------------------
er 1000000
op 1000000
qw 1000000
ty 1000000
ui 1000000


The above execution statistic ain't bad.

If you are still not happy with the performance and the number of
indexed reads, a compound index on (REFA, REFWROD) might reduce the
indexed reads on B even further (haven't tried though).

You might also dig into I/O statistics to possibly see something to
increase the page cache etc.


Is your example something real from your production scenario?

--
With regards,
Thomas Steinmaurer
http://www.upscene.com/

>> No. IMHO, the optimizer is doing a good job. It uses indexes with a good
>> selectivity and first horizontally filter table A by using A_IDX1 and
>> uses that already filtered "stream" to join table B via index B_IDX1.
>>
>> Just for the records: Firebird can join two indexes on the same table
>> via a bitmap vector (as shown in the execution plan before re-computing
>> index statistics), but in your case, the optimizer uses a different
>> path, which executes the query pretty fast.
>>
>> Do you have any real performance issues to solve?
>>
>
> yes i have a big problem in the production database because i have a big number of read on table B because the second index is not used so each time it found a line in A, it reads all relational lines in TABLE B.
> in production database i have one million lines in table a and near 10 millions in table B.






> ------------------------------------
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> Visit http://www.firebirdsql.org and click the Resources item
> on the main (top) menu. Try Knowledgebase and FAQ links !
>
> Also search the knowledgebases at http://www.ibphoenix.com
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
> Yahoo! Groups Links
>
>
>