Hi Tim,

Below is an example (one of many I can create) of a query and its execution
plan that have me puzzled.

The table contains 20000 rows and the code column always contains zero or
above, so the query result need to be 20000 rows. And it does (of course).

But, I don't understand the execution plan stating that 19733 keys have been
scanned. Should this not be 20000 keys? Did it not scan the remaining 267
keys in order to know the code is indeed zero or above. I just don't get it.
Could you please explain? Thanks.

Best regards,
Fons


================================================================================
SQL Query (Executed by ElevateDB 1.08 Build 1)

Note: The SQL  shown here is generated by ElevateDB and may not be exactly
the
same as the SQL that was originally entered.  However, none of the
differences
alter the execution results in any way.
================================================================================

SELECT ALL
"id" AS "id"
FROM "lijstdata"
WHERE "code" >= 0

Source Tables
-------------

lijstdata: 20000 rows

Result Set
----------

The result set was insensitive and read-only
The result set consisted of zero or more rows

Filtering
---------

The following filter condition was applied to the lijstdata table:

"code" >= 0 [Index scan: 19733 keys, 307200 bytes estimated cost]

================================================================================
20000 row(s) returned in 0,514 secs
================================================================================

Fons


267 duplicated codes?

Roy Lambert

Hi Roy,

> 267 duplicated codes?

No, I am pretty sure it is the "statistics in the indexes" which is
different from EDB than it was in DBISAM, in that the "cost" is an
estimation and not 100% accurate. No problem, cause indexes are faster than
in DBISAM and I do not need 100% accurate "costs" as long as the query
result is 100% correct  

Best regards,
Fons

Fons,

<< No, I am pretty sure it is the "statistics in the indexes" which is
different from EDB than it was in DBISAM, in that the "cost" is an
estimation and not 100% accurate. No problem, cause indexes are faster than
in DBISAM and I do not need 100% accurate "costs" as long as the query
result is 100% correct  >>

Yep, you nailed it.  The EDB cost is an estimation, whereas with DBISAM it
was exact.  The beauty of the estimates is that they "scale" proportionally
in terms of accuracy.  As the number of rows returned decreases, the
estimates get more and more accurate in a fairly linear proportion.   *And*
they don't cost anything extra in terms of insert/update/delete I/O like
they did with DBISAM.

--
Tim Young
Elevate Software
www.elevatesoft.com