New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
order by or max with index is very slow [CORE2889] #3273
Comments
Modified by: @dyemanovassignee: Dmitry Yemanov [ dimitr ] |
Commented by: Milan Tomes (tomisoft) I'm really curious about plans returned by server. |
Commented by: flyingfb (flyingfb) statement 1: PLAN (Wool_InSheet ORDER Wool_InSheet_IDX2 INDEX (Wool_InSheet_IDX1)) |
Modified by: @dyemanovstatus: Open [ 1 ] => In Progress [ 3 ] |
Commented by: Derryck welas (welas) also affects "GROUP BY" performance in FB2.5/classic RC2: SELECT O_ORDERDATE ,count(*) FROM ORDERS where O_CLERK='Clerk#000005122' group by 1 SELECT O_ORDERDATE+0 ,count(*) FROM ORDERS where O_CLERK='Clerk#000005122' group by 1 |
Modified by: @dyemanovstatus: In Progress [ 3 ] => Open [ 1 ] |
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! |
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
The text was updated successfully, but these errors were encountered: