|Subject||Re: [firebird-support] Re: How firebird use indexes with inner join|
> thanks for your reponse,Changed the date/time interval to another 1 hour window with data.
>> 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
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:
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?
>> 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