Skip to content
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

Open
firebird-automations opened this issue Feb 26, 2010 · 7 comments
Open

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

firebird-automations opened this issue Feb 26, 2010 · 7 comments

Comments

@firebird-automations
Copy link
Collaborator

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
Copy link
Collaborator Author

Modified by: @dyemanov

assignee: Dmitry Yemanov [ dimitr ]

@firebird-automations
Copy link
Collaborator Author

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
Copy link
Collaborator Author

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
Copy link
Collaborator Author

Modified by: @dyemanov

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

@firebird-automations
Copy link
Collaborator Author

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
Copy link
Collaborator Author

Modified by: @dyemanov

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

@firebird-automations
Copy link
Collaborator Author

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!

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

2 participants