Hi Antti,
are you frequently updating or deleting this table?
there may be a lot of old versions data&index pages for this table.

is your HW ok also? is your HD/OS performce in expected speed for
other operations?

to diagnose your problem, you may download free version(personel) of
ibexpert from www.ibexpert.com.
then, install it and define your db file to it.
then, run pull down menu/services/database statistics.
or
run ISQL and connect to your db

>set stat

run your query
statistical report will be at bottom of your result dumps.

for HW diagnose you may download sisofts sandra.

Ali

--- In firebird-support@yahoogroups.com, "Antti Nivala"
<antti.nivala@...> 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.

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