[Firebird-devel] [ firebird-Bugs-223060 ] Slow processing of GREATER-THEN operator

[SourceForge.net <no...@so...>]

Bugs item #223060, was opened at 2000-11-21 15:20
Message generated for change (Comment added) made by hvlad
You can respond by visiting: 
https://sourceforge.net/tracker/?func=detail&atid=109028&aid=223060&group_id=9028

Please note that this message will contain a full copy of the comment thread,
including the initial issue submission, for this request,
not just the latest update.
Category: Core Engine
Group: Fixed v2.0
Status: Closed
Resolution: Fixed
Priority: 5
Submitted By: Ivan Prenosil (prenosil)
Assigned to: Vlad Horsun (hvlad)
Summary: Slow processing of GREATER-THEN operator

Initial Comment:
Suppose you have table

  CREATE TABLE tab (field INTEGER  CHECK(field>=0) );
  CREATE INDEX idx ON tab (field);

with lots of rows where field=0.
When you execute these two statements

  SELECT * FROM tab WHERE field >= 1
  SELECT * FROM tab WHERE field > 0

they both will return the same result set,
they both will use the same plan,
however the second one (with >) will be much slower.
The difference is caused by internal processing
of ">=" and ">" clauses when using indexes.

When you execute
  SELECT * FROM tab WHERE field >= 1
IB will use index to locate all values >=1, o.k.

When you execute
  SELECT * FROM tab WHERE field > 0
IB will use index to locate all values >=0 !!!
i.e. ALL rows from our test table !!!


Why nobody noticed it ?
- for columns with more even distribution of values
  the speed difference is minimal
- IB usually evaluates condition twice
  1. it first locates row by index
     (not always correctly as you can see)
  2. after fetching row it evaluates the condition again
     (in this case it discards rows where field=0,
     that should have been filtered out by index)


How can you verify this theory ?
- start (W)ISQL
  and update one row where value is 0.
  Do not commit.

- start another (W)ISQL and start transaction
  SET TRANSACTION READ COMMITTED NO RECORD_VERSION NO WAIT

  Now, first execute faster query (where field >= 1).
  You will get correct answer.

  Then execute slow query (where field > 0).
  You will get "lock conflict" error.
  It means that IB tries to internally fetch
  row that does not meet criteria (value>0)
  and that should have been skipped when using index properly.


----------------------------------------------------------------------

>Comment By: Vlad Horsun (hvlad)
Date: 2005-10-31 11:11

Message:
Logged In: YES 
user_id=879552

It is fixed after Alpha3. 
You can download daily snaphot here

http://firebird.sourceforge.net/download/snapshot_builds/win/Fi
rebird-2.0.0.win32-snapshot.zip

----------------------------------------------------------------------

Comment By: Ivan Prenosil (prenosil)
Date: 2005-10-31 11:02

Message:
Logged In: YES 
user_id=89535

It is NOT fixed in FB2-Alpha3.

----------------------------------------------------------------------

Comment By: Vlad Horsun (hvlad)
Date: 2005-10-28 00:56

Message:
Logged In: YES 
user_id=879552

It must be fixed now. Please test it

----------------------------------------------------------------------

You can respond by visiting: 
https://sourceforge.net/tracker/?func=detail&atid=109028&aid=223060&group_id=9028