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
Operations

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

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


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


 All   Comments   Change History   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
There are no subversion log entries for this issue yet.