Issue Details (XML | Word | Printable)

Key: CORE-2889
Type: Bug Bug
Status: Open Open
Priority: Major Major
Assignee: Dmitry Yemanov
Reporter: flyingfb
Votes: 1
Watchers: 4

If you were logged in you would be able to see more operations.
Firebird Core

order by or max with index is very slow

Created: 26/Feb/10 04:08 AM   Updated: 02/Jan/14 10:16 AM
Component/s: Engine
Affects Version/s: 2.1.3, 2.5 RC2
Fix Version/s: None

Server Version: LI-V2.1.3.18185 Firebird 2.1
Server Implementation: Firebird/linux Intel
Service Version: 2

 Description  « Hide
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

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

 All   Comments   Change History   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Milan Tomes added a comment - 26/Feb/10 07:55 AM
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

flyingfb added a comment - 27/Feb/10 05:37 AM
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))

Derryck welas added a comment - 12/May/10 08:55 AM

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

flyingfb added a comment - 26/Dec/13 02:40 AM - edited
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!