Hi Antti,

i have no big db file at home, i couldn't test very well.
but,
this command selects different PLAN and does less fetch for me.

DELETE FROM PV_TYPE
WHERE
PV = 2 AND
( SELECT min(PV) FROM DV_PV WHERE PV = 2 ) IS NULL

Subselect will use PLAN ( DV_PV ORDER MY_PV_INDEX).

Otherwise it will use PLAN(DV_PV INDEX MY_PV_INDEX).

and work with different fetchs for me.
i have no idea about detailed internals and traversing strategy,
i never analysed FB sources.

Regards.
Ali

--- In firebird-support@yahoogroups.com, "Antti Nivala"
<antti.nivala@...> wrote:

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