person wrote:
I have a big table (about 20.000.000 records). Running a simple "SELECT FIRST 1" query or a NOT EXISTS check against this table takes a surprisingly long time. I am using FB 2.0 RC 3.
Example:
SELECT FIRST 1 *
FROM DV_PV
WHERE
PROPERTYVALUE = 2
FB chooses the following plan: "PLAN (DV_PV INDEX (IX_DV_PV_PV))"
IX_DV_PV_PV has poor selectivity because there are many duplicate PROPERTYVALUE values (about 1.000.000 records where PROPERTYVALUE = 2). But I don't understand why poor selectivity would make the "FIRST 1" type of a query slow. I also tried adding the PK to the end of the poor index but that did not make any difference to the query speed.
Why does it take so long? I think FB should only need to visit the index to find the first matching record?
Dmitry Yemanov answers:
Records are fetched from a table based on the bitmap filled by the index scan. I.e. no record can be fetched until the index scan completes for all existing matches. For a non-selective index, the scan may take quite a long time.