Submitted By: centros
Having some active indexes with low selectivity is
killing condition for some SELECT statements. It is a
problem of a planner, that such bad indexes are used in
a plan. SELECT execution is about 10 to 50 times
slower, compared to a situation, when such indexes are
set inactive. I am testing on FB 2.0 Beta 2, the same
problem was on FB 1.5. The same SELECT on IB 6.0 or IB
7.5 executes nice fast, even with such bad indexes. IB
planner is clever enough not to use indexes with low
selectivity in a plan.
Example of a SELECT statement:
left outer join SKLPOH on
where SKLPOHDATZADAL >= :DATOD AND SKLPOHDATZADAL <=
:DATDO AND SKLPOHTYP=10 AND
SKLPOHDETAIL.recordactive='1' and SKLPOH.recordactive =
'1' and SKLPOHSTORNOVANO='0'
The indexes with low selectivity are set on
SKLPOH.recordactive and SKLPOHDETAIL.recordactive.
These items have only three possible values, 0, 1 or 2.
Most of them have value 1.
Both SKLPOH and SKLPOHDETAIL tables have about 200.000
rows each. Overal database size is about 300 MB.
If someone is interested, I can post the database file