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

Too many fetches on some patterns with STARTING WITH [CORE3840] #4182

Open
firebird-automations opened this issue May 3, 2012 · 2 comments

Comments

@firebird-automations
Copy link
Collaborator

Submitted by: Alexander Sumskiy (slay2012)

Attachments:
CORE-3840.sql

SET NAMES UTF8;

CREATE DATABASE 'localhost:testdb'
USER 'SYSDBA' PASSWORD 'masterkey'
PAGE_SIZE 4096
DEFAULT CHARACTER SET UTF8 COLLATION UTF8;

CREATE TABLE test (
id INTEGER NOT NULL,
terms VARCHAR(20) NOT NULL COLLATE UNICODE_CI
);

INSERT INTO test (id, terms) VALUES (1, 'ааа');
INSERT INTO test (id, terms) VALUES (2, 'абб');
INSERT INTO test (id, terms) VALUES (3, 'асс');
INSERT INTO test (id, terms) VALUES (4, 'абс');
INSERT INTO test (id, terms) VALUES (5, 'www');
INSERT INTO test (id, terms) VALUES (6, 'wee');
INSERT INTO test (id, terms) VALUES (7, 'wwe');

COMMIT WORK;

ALTER TABLE test ADD CONSTRAINT pktest PRIMARY KEY (id);
CREATE UNIQUE INDEX itest_term ON test (terms);

Query 1 (several russian letters):
select * from test t where t.terms STARTING WITH 'аб'

PLAN (T INDEX (itest_term))

------ Performance info ------
Prepare time = 15ms
Execute time = 0ms
Avg fetch time = 0.00 ms
Current memory = 9 597 788
Max memory = 9 921 956
Memory buffers = 2 048
Reads from disk to cache = 0
Writes from cache to disk = 0
Fetches from cache = 9

Query 2 (one letter):
select * from test t where t.terms STARTING WITH 'а'

PLAN (T INDEX (itest_term))

------ Performance info ------
Prepare time = 0ms
Execute time = 0ms
Avg fetch time = 0.00 ms
Current memory = 9 598 272
Max memory = 9 921 956
Memory buffers = 2 048
Reads from disk to cache = 0
Writes from cache to disk = 0
Fetches from cache = 19

@firebird-automations
Copy link
Collaborator Author

Commented by: @aafemt

I've saved script in right encoding (which is obviously UTF-8) and fixed it a little to emphasize the problem.

@firebird-automations
Copy link
Collaborator Author

Modified by: @aafemt

Attachment: CORE3840.sql [ 12143 ]

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

1 participant