| Subject | FB 1.5 Query Optimiser Question |
|---|---|
| Author | vendee928 |
| Post date | 2013-07-31T18:36:39Z |
Hi,
My organisation is currently using FB 1.5 and I wondered if anyone would be explain some unexpected behaviour from the optimizer.
I have two tables. TABLE1 contains about 21k records and TABLE2 contains around 2.5m records. There are indexes on the joined fields, and both where clause fields. I've recalculated statistics on each.
The query below takes around 10 minutes to return with 500 records.
A) SELECT A.F1 FROM TABLE1 A INNER JOIN TABLE2 B ON A.F1=B.F1 WHERE A.CLAUSE1=0 AND B.CLAUSE2=100
(FB Suggested plan: PLAN JOIN (A INDEX (IDX_TABLE1),B INDEX (IDX_TABLE2,PK_TABLE2)):)
I've split this up and run the individual parts of the query with the following results.
B) SELECT * FROM TABLE1 WHERE CLAUSE1=0
(FB Suggested Plan: PLAN (TABLE1 INDEX (IDX_TABLE1)):)
Returns in < 1 second with 3800 records.
C) SELECT * FROM TABLE2 WHERE CLAUSE2=100
(FB Suggested Plan: PLAN TABLE2 INDEX (IDX_TABLE2)):)
Returns in < 1 second with 800k records.
I've also tried removing parts of the where clause:
D) SELECT A.F1 FROM TABLE1 A INNER JOIN TABLE2 B ON A.F1=B.F1
(FB Suggested Plan: PLAN JOIN (A NATURAL,B INDEX (PK_TABLE2));)
Returns in < 1 second with 21k records.
E) SELECT A.F1 FROM TABLE1 A INNER JOIN TABLE2 B ON A.F1=B.F1 WHERE A.CLAUSE1=0
(FB Suggested Plan: PLAN JOIN (A INDEX (IDX_NL_TABLE1),B INDEX (PK_TABLE2));)
Returns in < 1 second with 500 records.
Forcing the initial query to use the suggested plan from query E) (Even though it's not now using the index for CLAUSE2 on the TABLE2 table):
F) SELECT A.F1 FROM TABLE1 A INNER JOIN TABLE2 B ON A.F1=B.F1 WHERE A.CLAUSE1=0 AND B.CLAUSE2=100
(Manually forced plan: PLAN JOIN (A INDEX (IDX_NL_TABLE1),B INDEX (PK_TABLE2));)
Returns in < 1 second with 500 records.
Any possible explanation would be very welcome. My guess is that there's so many records (Around a third) on TABLE2 with a CLAUSE2 value of 100 that using the index on this field as part of the select is somehow slowing things down to a crawl. The selectivity of this index is 0.2. However, using the index on the table alone is still relatively fast, hence my confusion.
Thanks.
My organisation is currently using FB 1.5 and I wondered if anyone would be explain some unexpected behaviour from the optimizer.
I have two tables. TABLE1 contains about 21k records and TABLE2 contains around 2.5m records. There are indexes on the joined fields, and both where clause fields. I've recalculated statistics on each.
The query below takes around 10 minutes to return with 500 records.
A) SELECT A.F1 FROM TABLE1 A INNER JOIN TABLE2 B ON A.F1=B.F1 WHERE A.CLAUSE1=0 AND B.CLAUSE2=100
(FB Suggested plan: PLAN JOIN (A INDEX (IDX_TABLE1),B INDEX (IDX_TABLE2,PK_TABLE2)):)
I've split this up and run the individual parts of the query with the following results.
B) SELECT * FROM TABLE1 WHERE CLAUSE1=0
(FB Suggested Plan: PLAN (TABLE1 INDEX (IDX_TABLE1)):)
Returns in < 1 second with 3800 records.
C) SELECT * FROM TABLE2 WHERE CLAUSE2=100
(FB Suggested Plan: PLAN TABLE2 INDEX (IDX_TABLE2)):)
Returns in < 1 second with 800k records.
I've also tried removing parts of the where clause:
D) SELECT A.F1 FROM TABLE1 A INNER JOIN TABLE2 B ON A.F1=B.F1
(FB Suggested Plan: PLAN JOIN (A NATURAL,B INDEX (PK_TABLE2));)
Returns in < 1 second with 21k records.
E) SELECT A.F1 FROM TABLE1 A INNER JOIN TABLE2 B ON A.F1=B.F1 WHERE A.CLAUSE1=0
(FB Suggested Plan: PLAN JOIN (A INDEX (IDX_NL_TABLE1),B INDEX (PK_TABLE2));)
Returns in < 1 second with 500 records.
Forcing the initial query to use the suggested plan from query E) (Even though it's not now using the index for CLAUSE2 on the TABLE2 table):
F) SELECT A.F1 FROM TABLE1 A INNER JOIN TABLE2 B ON A.F1=B.F1 WHERE A.CLAUSE1=0 AND B.CLAUSE2=100
(Manually forced plan: PLAN JOIN (A INDEX (IDX_NL_TABLE1),B INDEX (PK_TABLE2));)
Returns in < 1 second with 500 records.
Any possible explanation would be very welcome. My guess is that there's so many records (Around a third) on TABLE2 with a CLAUSE2 value of 100 that using the index on this field as part of the select is somehow slowing things down to a crawl. The selectivity of this index is 0.2. However, using the index on the table alone is still relatively fast, hence my confusion.
Thanks.