Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Plan Indexes different in Firebird 1.5 and Firebird 2.x and slow [CORE2614] #3024

Open
firebird-automations opened this issue Sep 2, 2009 · 7 comments

Comments

@firebird-automations
Copy link
Collaborator

Submitted by: Pedro Rodrigues (pemisaro)

Attachments:
BD 1.5.zip
BD 2.1.zip
Sql.txt

Votes: 4

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 ?

@firebird-automations
Copy link
Collaborator Author

Modified by: Pedro Rodrigues (pemisaro)

Attachment: BD 1.5.zip [ 11490 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: Pedro Rodrigues (pemisaro)

Attachment: BD 2.1.zip [ 11491 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: Pedro Rodrigues (pemisaro)

Attachment: Sql.txt [ 11492 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

assignee: Dmitry Yemanov [ dimitr ]

security: Developers [ 10012 ] =>

@firebird-automations
Copy link
Collaborator Author

Commented by: @dyemanov

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.

@firebird-automations
Copy link
Collaborator Author

Commented by: @dyemanov

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.

@firebird-automations
Copy link
Collaborator Author

Commented by: @livius2

Hi, when i click on attachments e.g. sql.txt i got
"A system error has occurred."

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment