order by or max with index is very slow [CORE2889]

[firebird-automations]

Submitted by: flyingfb (flyingfb)

Votes: 1

First, meta data is given:

CREATE TABLE "Wool_InSheet" ( "StorageName" VARCHAR(12), "InDate" DATE, "InCode" VARCHAR(10), "InVoiceNo" VARCHAR(10), "KeeperCode" VARCHAR(10), "InTypeCode" CHAR(2), "DeptCode" VARCHAR(15), "DeptName" VARCHAR(40), "Kind" VARCHAR(10), "PackNumber" VARCHAR(10), "YarnCount" VARCHAR(10), "LotCode" VARCHAR(20), "ArticleCode" VARCHAR(20), "ColorCode" VARCHAR(20), "Trait" VARCHAR(12), "MixColor" VARCHAR(20), "IsColor" CHAR(1), "WQty" NUMERIC(18,2), "NQty" NUMERIC(18,2), "SQty" NUMERIC(18,3), "Amount" INTEGER, "Note" VARCHAR(100), "Status" VARCHAR(10), "Mark" VARCHAR(10), "SubCompanyName" VARCHAR(20), "Check" CHAR(1), "Sub" VARCHAR(10), "Place" VARCHAR(20), "PArea" VARCHAR(10), "MLotCode" VARCHAR(20), "PackMode" VARCHAR(10), "Packages" INTEGER, "Account" CHAR(1), "Wet" FLOAT, "Canal" VARCHAR(10), "ArticleName" VARCHAR(30), "TwistWay" VARCHAR(10), "Kind2" VARCHAR(10), "Mix" VARCHAR(20), "TInDate" DATE );

CREATE INDEX "Wool_InSheet_IDX1" ON "Wool_InSheet" ("LotCode"); CREATE INDEX "Wool_InSheet_IDX2" ON "Wool_InSheet" ("InDate"); CREATE DESCENDING INDEX "Wool_InSheet_IDX3" ON "Wool_InSheet" ("InDate");

Second,insert into 2M records. Wool_InSheet_IDX1 selectivity 0.000037 Wool_InSheet_IDX2 selectivity 0.000587 Wool_InSheet_IDX3 selectivity 0.000587

Result: statement 1: select * from "Wool_InSheet" where "LotCode"='D99054' order by "InDate" statement 2: select * from "Wool_InSheet" where "LotCode"='D99054' order by "InDate"+0 statement 1 is about five to ten times slower then statement 2, both return 9 records

statement 3: select max("InDate") from "Wool_InSheet" where "LotCode"='D99054' statement 4: select max("InDate"+0) from "Wool_InSheet" where "LotCode"='D99054' statement 3 is about five to ten times slower then statement 4

[firebird-automations]

Modified by: @dyemanov

assignee: Dmitry Yemanov \[ dimitr \]

[firebird-automations]

Commented by: Milan Tomes (tomisoft)

I'm really curious about plans returned by server. Just my tip: statement 1 - use indexes Wool_InSheet_IDX1 and Wool_InSheet_IDX2 (for order) statement 2 - use only Wool_InSheet_IDX1 statement 3 - use indexes Wool_InSheet_IDX1 and Wool_InSheet_IDX3 statement 4 - use only index Wool_InSheet_IDX1

[firebird-automations]

Commented by: flyingfb (flyingfb)

statement 1: PLAN (Wool_InSheet ORDER Wool_InSheet_IDX2 INDEX (Wool_InSheet_IDX1)) statement 2: PLAN SORT ((Wool_InSheet INDEX (Wool_InSheet_IDX1))) statement 3: PLAN (Wool_InSheet ORDER Wool_InSheet_IDX3 INDEX (Wool_InSheet_IDX1)) statement 4: PLAN (Wool_InSheet INDEX (Wool_InSheet_IDX1))

[firebird-automations]

Modified by: @dyemanov

status: Open \[ 1 \] =\> In Progress \[ 3 \]

[firebird-automations]

Commented by: Derryck welas (welas)

also affects "GROUP BY" performance in FB2.5/classic RC2: selectivity O_CLERK 0.000167 selectivity O_ORDERDATE 0.000416

SELECT O_ORDERDATE ,count(*) FROM ORDERS where O_CLERK='Clerk#⁠000005122' group by 1 >PLAN (ORDERS ORDER ORDERS_ORDERDATE INDEX (IDX_ORDERS1)) >10137 fetches, 0 marks, 8483 reads, 0 writes. >0 inserts, 0 updates, 0 deletes, 1447 index, 0 seq. >Delta memory: 184224 bytes. >Total execution time: 1.171s

SELECT O_ORDERDATE+0 ,count(*) FROM ORDERS where O_CLERK='Clerk#⁠000005122' group by 1 >PLAN SORT ((ORDERS INDEX (IDX_ORDERS1))) >2979 fetches, 0 marks, 1606 reads, 0 writes. >0 inserts, 0 updates, 0 deletes, 1443 index, 0 seq. >Delta memory: 108896 bytes. >Total execution time: 0.047s

[firebird-automations]

Modified by: @dyemanov

status: In Progress \[ 3 \] =\> Open \[ 1 \]

[firebird-automations]

Commented by: flyingfb (flyingfb)

in firebird2.52, i guess the problem is solved. a similar "order by" problem exists in firebird2.11,today i test it in firebird2.52, the problem disappear!