Issue Details (XML | Word | Printable)

Key: CORE-5421
Type: Bug Bug
Status: Closed Closed
Resolution: Fixed
Priority: Major Major
Assignee: Dmitry Yemanov
Reporter: Andrei Kireev
Votes: 2
Watchers: 7
Operations

If you were logged in you would be able to see more operations.
Firebird Core

Performance degradation in FB 3.0.2 compared to FB 2.5.7

Created: 15/Dec/16 09:21 AM   Updated: 05/Jan/17 06:04 PM
Component/s: Engine
Affects Version/s: 3.0.1
Fix Version/s: 3.0.2, 4.0 Alpha 1

Environment: Windows 10

QA Status: Done successfully


 Description  « Hide
Here is the query:

  SELECT FIRST(1)
    doc.id, doc.documentdate
  FROM
    usr$wg_taxation card
      JOIN gd_document doc ON doc.id = card.documentkey
  WHERE
    card.usr$emplkey = :emplkey
    AND
    doc.documentdate <= :begindate
  ORDER BY
    doc.documentdate DESC

Table usr$wg_taxation has 1 record, table gd_document -- 7.5 millions.

Statistics for PK of usr$wg_taxation -- 1.
Statistics for PK of GD_DOCUMENT -- 1.3158557976567E-7
Statistics for GD_X_DOCUMENT_DOCUMENTDATE index -- 0.00020177561964374.

FB 2.5.7 execution time less than 1 ms.

Query plan: SORT (JOIN (CARD INDEX (USR$FKWG_TAXATION951), DOC INDEX (RDB$PRIMARY112)))

FB 3.0.2 execution time 13 156 ms.

Query plan:

Select Expression
    -> First N Records
        -> Nested Loop Join (inner)
            -> Filter
                -> Table "GD_DOCUMENT" as "DOC" Access By ID
                    -> Index "GD_X_DOCUMENT_DOCUMENTDATE" Range Scan (lower bound: 1/1)
            -> Filter
                -> Table "USR$WG_TAXATION" as "CARD" Access By ID
                    -> Bitmap
                        -> Index "RDB$PRIMARY502" Unique Scan





 All   Comments   Change History   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
José R López added a comment - 27/Dec/16 01:59 PM
It seems that this issue is the same described in CORE-5310 and comments (Incorrect PLAN using in Firebird 3 making it slow): Firebird 3 is sometimes choosing the index with less selectivity.