Issue Details (XML | Word | Printable)

Key: CORE-2614
Type: Bug Bug
Status: Open Open
Priority: Major Major
Assignee: Dmitry Yemanov
Reporter: Pedro Rodrigues
Votes: 4
Watchers: 5
Operations

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

Plan Indexes different in Firebird 1.5 and Firebird 2.x and slow

Created: 02/Sep/09 06:23 AM   Updated: 09/Oct/17 06:06 AM
Component/s: API / Client Library
Affects Version/s: 2.1.2, 2.5 Beta 1, 2.5 Beta 2, 2.1.3
Fix Version/s: None

File Attachments: 1. Zip Archive BD 1.5.zip (8.23 MB)
2. Zip Archive BD 2.1.zip (8.24 MB)
3. Text File Sql.txt (0.3 kB)

Environment: Windows or Linux


 Description  « Hide
   I have a Sql that is very slowly in Firebird 2.x and fast in Firebird 1.5, firebird 2.1 also sugest another Plan different then Firebird 1.5.
   The file BD 1.5.zip in attachment is the BD in Firebird 1.5
   The file BD 2.1.zip in attachment is the BD in Firebird 2.1.2.
   The file SQL.txt is the SQL that I made in both version, and the performances are very different, in firebird 1.5 is very faster, why ?



 All   Comments   Change History   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Dmitry Yemanov added a comment - 02/Sep/09 08:29 PM - edited
I have to admit this is a bad example, because the SQL query is wrong. When you combine LEFT JOIN with the WHERE clause against the right table, it effectively converts your query into an inner join but with a pre-defined join order (A->M, which is a bad choice in this case). If you really intended to use an inner join, then write that explicitly and you'll get better performance on both v1.5 and v2.x. Otherwise, the query should be written as:

  From Artigo A
  Left Outer Join Movimentos_Produtos M
    On (A.Codigo_Arm = M.Armazem and A.Referencia = M.Prod_Ref and M.Data_Doc <= :D)

in order to produce an expected result.

Dmitry Yemanov added a comment - 02/Sep/09 08:37 PM
Regardless the above, I agree that the usage of the index MOVIMENTOS_PRODUTOS_IDX04 looks like a bad idea. The v1.5 optimizer uses a heuristical approach and rejects that index because its selectivity is much worse than the one for MOVIMENTOS_PRODUTOS_IDX01. The v2.x optimizer uses a cost based approach instead and considers it being an useful addition allowing to reduce the effective stream cardinality (number of rows to read from M). But with your data, this additional predicate doesn't help much, reducing the cardinality by about 10% only, while actually costing more.

Karol Bieniaszewski added a comment - 09/Oct/17 06:06 AM
Hi, when i click on attachments e.g. sql.txt i got
"A system error has occurred."