UTF8 Collation UNICODE_CI_AI not working as expected causing poor performance
in database
-----------------------------------------------------------------------------------------
Key: CORE-5947
URL: http://tracker.firebirdsql.org/browse/CORE-5947
Project: Firebird Core
Issue Type: Bug
Components: Charsets/Collation
Affects Versions: 3.0.4
Environment: Windows x64 superserver, Linux x64 superserver
Reporter: Luis Forra
When migrating to utf8 with collation UNICODE_CI_AI I starting to note major
slowdown in performance, the problem is the full scan of the index when there
is more than one varchar in the index and is not unique
My original question in stackoverflow
https://stackoverflow.com/questions/52810369/firebird-3-0-4-unicode-ci-ai-index-problems
Example:
CREATE TABLE TEST_UNICODE (
S1 VARCHAR(10) CHARACTER SET UTF8 NOT NULL COLLATE UNICODE,
S2 VARCHAR(10) CHARACTER SET UTF8 NOT NULL COLLATE UNICODE
);
CREATE TABLE TEST_UNICODE_CI_AI (
S1 VARCHAR(10) CHARACTER SET UTF8 NOT NULL COLLATE UNICODE_CI_AI,
S2 VARCHAR(10) CHARACTER SET UTF8 NOT NULL COLLATE UNICODE_CI_AI
);
INSERT INTO TEST_UNICODE (S1, S2) VALUES ('A', 'A');
INSERT INTO TEST_UNICODE (S1, S2) VALUES ('A', 'B');
INSERT INTO TEST_UNICODE (S1, S2) VALUES ('B', 'A');
INSERT INTO TEST_UNICODE (S1, S2) VALUES ('B', 'B');
COMMIT WORK;
INSERT INTO TEST_UNICODE_CI_AI (S1, S2) VALUES ('A', 'A');
INSERT INTO TEST_UNICODE_CI_AI (S1, S2) VALUES ('A', 'B');
INSERT INTO TEST_UNICODE_CI_AI (S1, S2) VALUES ('B', 'A');
INSERT INTO TEST_UNICODE_CI_AI (S1, S2) VALUES ('B', 'B');
COMMIT WORK;
CREATE INDEX TEST_UNICODE ON TEST_UNICODE (S1, S2);
CREATE INDEX TEST_UNICODE_CI_AI ON TEST_UNICODE_CI_AI (S1, S2);
COMMIT WORK;
SELECT S1,S2 FROM test_unicode WHERE S1 = 'B' AND S2 = 'A'
UNION ALL
SELECT S1,S2 FROM test_unicode_ci_ai WHERE S1 = 'B' AND S2 = 'A'
The plan for this query is:
Select Expression
-> Union
-> Filter
-> Table "TEST_UNICODE" Access By ID
-> Bitmap
-> Index "TEST_UNICODE" Range Scan (full match)
-> Filter
-> Table "TEST_UNICODE_CI_AI" Access By ID
-> Bitmap
-> Index "TEST_UNICODE_CI_AI" Range Scan (partial match:
1/2)
I get 2 indexed reads with test_unicode_ci_ai and 1 indexed read with
test_unicode, with millions of records the problem escalates.
The workaround is to create the index unique by adding a numeric field in the
end.
The biggest problem is with foreign keys
example:
CREATE TABLE M_UNICODE (
S1 VARCHAR(10) CHARACTER SET UTF8 NOT NULL COLLATE UNICODE,
S2 VARCHAR(10) CHARACTER SET UTF8 NOT NULL COLLATE UNICODE
);
CREATE TABLE D_UNICODE (
S1 VARCHAR(10) CHARACTER SET UTF8 NOT NULL COLLATE UNICODE,
S2 VARCHAR(10) CHARACTER SET UTF8 NOT NULL COLLATE UNICODE
);
CREATE TABLE M_CI_AI (
S1 VARCHAR(10) CHARACTER SET UTF8 NOT NULL COLLATE UNICODE_CI_AI,
S2 VARCHAR(10) CHARACTER SET UTF8 NOT NULL COLLATE UNICODE_CI_AI
);
CREATE TABLE D_CI_AI (
S1 VARCHAR(10) CHARACTER SET UTF8 NOT NULL COLLATE UNICODE_CI_AI,
S2 VARCHAR(10) CHARACTER SET UTF8 NOT NULL COLLATE UNICODE_CI_AI
);
INSERT INTO M_UNICODE (S1, S2) VALUES ('A', 'A');
INSERT INTO M_UNICODE (S1, S2) VALUES ('A', 'B');
INSERT INTO M_CI_AI (S1, S2) VALUES ('A', 'A');
INSERT INTO M_CI_AI (S1, S2) VALUES ('A', 'B');
set term ^ ;
execute block
as
declare variable i bigint = 1000;
begin
while (i > 0) do
begin
insert into d_unicode (s1,s2) values ('A','A');
insert into d_ci_ai (s1,s2) values ('A','A');
i = i-1;
end
insert into d_unicode (s1,s2) values ('A','B');
insert into d_ci_ai (s1,s2) values ('A','B');
end^
set term ; ^
ALTER TABLE M_UNICODE ADD CONSTRAINT M_U PRIMARY KEY (S1, S2);
ALTER TABLE M_CI_AI ADD CONSTRAINT M_CI_AI PRIMARY KEY (S1, S2);
commit work;
ALTER TABLE D_UNICODE ADD CONSTRAINT D_U FOREIGN KEY (S1, S2) REFERENCES
M_UNICODE (S1, S2) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE D_CI_AI ADD CONSTRAINT D_CI_AI FOREIGN KEY (S1, S2) REFERENCES
M_CI_AI (S1, S2) ON DELETE CASCADE ON UPDATE CASCADE;
commit work;
if i update the parent table with the collation UNICODE with:
update m_unicode set s2 = 'C' where s1 = 'A' and s2 = 'B'
it work as expected there is 2 indexed reads in the parent and child table
But if I update the parent table with the collation UNICODE_CI_AI with:
update m_ci_ai set s2 = 'C' where s1 = 'A' and s2 = 'B';
It reads all the index of the child table, in this case 2002 indexed reads.
In this case if I want to maintain the collation UNICDE_AI_CI I don't see any
workaround.
Adriano believes this bug is related to CORE-5940
Thank You
;
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://tracker.firebirdsql.org/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
