Index selectivity
-----------------
Key: CORE-5146
URL: http://tracker.firebirdsql.org/browse/CORE-5146
Project: Firebird Core
Issue Type: Bug
Affects Versions: 3.0 Beta 2
Reporter: Marco Van Kan
While testing FB 3.0 (RC2) we encountered a bad performance on a simple query:
SELECT MIN(HE.DATEVALUE)
FROM HOURITEMS HI INNER JOIN DIHOURENTRIES HE ON HI.HOURITEMID = HE.HOURITEMID
WHERE HI.PROJECTID = 30762
Indexes are on all fields of the query above (HI.HOURITEMID, HE.HOURITEMID,
HI.PROJECTID AND HE.DATEVALUE)
FB 2.5 handles this query very fast (0.07sec). FB 3.0 is much slower (1.6sec).
This performance lag is caused by 'weird' use of index. Below a comparison:
FB 2.5 uses the indexes on HI.PROJECTID AND HE.HOURITEMID
FB 3.0 RC2 uses the indexes on HI.HOURITEMID AND HE.DATEVALUE >> HI.PROJECTID
is ignored!
A 'workaround' for FB 3.0 could be something like:
SELECT MIN(HE.DATEVALUE)
, COUNT(0) AS DUMMY
FROM HOURITEMS HI INNER JOIN DIHOURENTRIES HE ON HI.HOURITEMID = HE.HOURITEMID
WHERE HI.PROJECTID = 30762
--
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
------------------------------------------------------------------------------
Transform Data into Opportunity.
Accelerate data analysis in your applications with
Intel Data Analytics Acceleration Library.
Click to learn more.
http://pubads.g.doubleclick.net/gampad/clk?id=278785111&iu=/4140
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
