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.

The returned stats are:

Current memory = 33386968
Delta memory = 9152540
Max memory = 33393780
Elapsed time= 0.48 sec
Buffers = 65535
Reads = 0
Writes 0
Fetches = 1309

QUESTIONS:

1. Why does it take so long? I think FB should only need to visit the index to find the first matching record.

2. What does "Fetches = 1309" mean? Does it mean that FB has fetched 1309 record from the DV_PV table?

3. What does "Current memory = 33386968" mean? I don't seem to get this figure to rise even though Buffers = 65535 and page size is 8192. In Task Manager I can see that FB is taking about 512 MB, which is the intention, but I don't understand the memory amount reported by SET STATS.

My main intention is to do a DELETE from another table if a corresponding record in the DV_PV table does not exist. The "DELETE FROM PV WHERE PV.PropertyValue = 2 AND NOT EXISTS( SELECT * FROM DV_PV WHERE PropertyValue = 2 )" query is just as slow as the above SELECT FIRST 1 query and is making the delete take much too long.

Antti