Dmitry Yemanov wrote:

> 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.

Thank you. This fully explains the problem I am experiencing. Is there a
workaround, i.e. is there a way to make a pure index-based existence
check based on the "PV = 2" condition when there are lots of (e.g.
1.000.000) records for which this is true? The table has 26.000.000
records so a natural scan would be much worse in many cases.

In practice, I am looking for a way to delete a record from table
PV_TYPE if that record is not referenced by any record in the DV_PV
table. For example, when deleting a record from PV_TYPE, I currently do
DELETE FROM PV_TYPE
WHERE
PV = <x> AND
NOT EXISTS ( SELECT 1 FROM DV_PV WHERE PV = <x> )

This takes 0 ms if there are not many matching records in DV_PV, but 400
ms if there are 1.000.000 matching records in DV_PV. My database has
just been backed up and restored. The point is, as Dmitry said, that the
EXISTS check reads lots of data from the index in that case. Is there a
way to avoid that, i.e. utilize the index for a pure existence check? I
would want FB to stop reading the index as soon as it sees that at least
one record matches.

The workaround of having a manual count field is one possibility, but I
am still looking for a more efficient and elegant solution. Is there
one?

Antti