Sub-optimal fuzzy index lookup for empty strings in compound indices
--------------------------------------------------------------------
Key: CORE-4099
URL: http://tracker.firebirdsql.org/browse/CORE-4099
Project: Firebird Core
Issue Type: Bug
Components: Engine
Affects Versions: 2.5.2 Update 1, 2.1.5 Update 1, 2.5.2, 2.1.5, 2.0.7,
2.5.1, 2.1.4, 2.5.0, 2.0.6, 3.0 Initial, 2.1.3, 2.1.2, 2.0.5, 2.1.1, 2.1.0
Reporter: Dmitry Yemanov
Priority: Minor
recreate table tab (col1 varchar(10), col2 varchar(10));
create index itab on tab (col1, col2);
execute block
as
declare cnt int = 1000;
begin
while (cnt > 0) do
begin
insert into tab (col1, col2) values (:cnt, :cnt);
cnt = cnt - 1;
end
end;
select * from tab where col1 = '' and col2 starting with trim('');
-- 1000 index reads to found zero rows
TRIM is used just to fool the optimizer so that both index segments were looked
up, STARTING WITH :PARAM could be used instead. The point here is to use an
empty string in all conditions (equality for leading segments and STARTING for
the trailing segment).
--
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
------------------------------------------------------------------------------
Introducing AppDynamics Lite, a free troubleshooting tool for Java/.NET
Get 100% visibility into your production application - at no cost.
Code-level diagnostics for performance bottlenecks with <2% overhead
Download for free and get started troubleshooting in minutes.
http://p.sf.net/sfu/appdyn_d2d_ap1
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
