Issue Details (XML | Word | Printable)

Key: CORE-1940
Type: Improvement Improvement
Status: Open Open
Priority: Major Major
Assignee: Unassigned
Reporter: Tim Kelly
Votes: 0
Watchers: 0
Operations

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

OUTER JOIN INEFFICIENT if foreign key exists (especially with views)

Created: 18/Jun/08 05:26 PM   Updated: 19/Jun/08 12:15 PM
Component/s: Engine
Affects Version/s: 2.1.0
Fix Version/s: None


 Description  « Hide
Unless my query performance analyzer is crap or I am being a loony, I think there is scope for greater efficiency by ignoring redundant tables which are outer joined:

Consider the following:

CREATE TABLE_A
(pk INTEGER, FIELD_A integer)

CREATE TABLE_B
(pk INTEGER, FIELD_B integer)

ALTER TABLE TABLE_A ADD PRIMARY KEY (PK);
ALTER TABLE TABLE_B ADD PRIMARY KEY (PK);

ALTER TABLE TABLE_B ADD FOREIGN KEY (PK) REFERENCES TABLE_A(PK);

SELECT TABLE_A.PK, FIELD_A FROM TABLE_A LEFT JOIN TABLE_B ON TABLE_A.PK = TABLE_B.PK

//According to performance analysis TABLE_B's index is queried even though clearly no fields are required from it and because it's left joined it can't affect the rows returned from table_A. The weird thing is that if the index doesn't exist then TABLE_B is optimized out! You may think big deal serve the silly DBA for writing such a stupid query, but consider:

CREATE VIEW VIEW_B
AS
SELECT TABLE_A.PK as PK, FIELD_A, FIELD_B FROM TABLE_A LEFT JOIN TABLE_B ON TABLE_A.PK = TABLE_B.PK

SELECT FIELD_A from VIEW_B

Not so silly now, but inefficient as TABLE_B's index is unnecessarily fetched (according to my analyzer). Can't we detect that TABLE_B is not actually required at all and optimize it out?


 All   Comments   Change History   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Tim Kelly made changes - 18/Jun/08 05:31 PM
Field Original Value New Value
Description Unless my query performance analyzer is crap or I am being a loony, I think there is scope for greater efficiency by ignoring redundant tables which are outer joined:

Consider the following:

CREATE TABLE_A
(pk INTEGER, FIELD_A integer)

CREATE TABLE_B
(pk INTEGER, FIELD_B integer)

SELECT TABLE_A.PK, FIELD_A, FIELD_B FROM TABLE_A LEFT JOIN TABLE_B ON TABLE_A.PK = TABLE_B.PK
//According to performance analysis TABLE_B is queried even though clearly no fields are required from it and because it's left joined it can't affect the rows returned from table_A. Can't we detect that it plays no role in the query and ignore it? When you start scaling this up I think it could have considerable performance implications?!

CREATE VIEW VIEW_A
AS
SELECT TABLE_A.PK, FIELD_A, FIELD_B FROM TABLE_A LEFT JOIN TABLE_B ON TABLE_A.PK = TABLE_B.PK

SELECT * FROM VIEW_A
//Same issue as above

CREATE VIEW VIEW_B
AS
SELECT TABLE_A.PK as PK, FIELD_A, FIELD_B FROM TABLE_A LEFT JOIN TABLE_B ON TABLE_A.PK = TABLE_B.PK

SELECT FIELD_A from VIEW_B
//Once again why do we need to go to the bother of linking TABLE_B it is redundant!?
Unless my query performance analyzer is crap or I am being a loony, I think there is scope for greater efficiency by ignoring redundant tables which are outer joined:

Consider the following:

CREATE TABLE_A
(pk INTEGER, FIELD_A integer)

CREATE TABLE_B
(pk INTEGER, FIELD_B integer)

SELECT TABLE_A.PK, FIELD_A FROM TABLE_A LEFT JOIN TABLE_B ON TABLE_A.PK = TABLE_B.PK

//According to performance analysis TABLE_B is queried even though clearly no fields are required from it and because it's left joined it can't affect the rows returned from table_A. You may think big deal serve the silly DBA for writing such a stupid query, but consider:

CREATE VIEW VIEW_B
AS
SELECT TABLE_A.PK as PK, FIELD_A, FIELD_B FROM TABLE_A LEFT JOIN TABLE_B ON TABLE_A.PK = TABLE_B.PK

SELECT FIELD_A from VIEW_B

Not so silly now, but inefficient as TABLE_B is unnecessarily queried (according to my analyzer). Can't we detect that TABLE_B is not actually required at all and optimize it out?
Tim Kelly made changes - 18/Jun/08 05:51 PM
Summary OUTER JOIN INEFFICIENT (especially with views) OUTER JOIN INEFFICIENT if foreign key exists (especially with views)
Description Unless my query performance analyzer is crap or I am being a loony, I think there is scope for greater efficiency by ignoring redundant tables which are outer joined:

Consider the following:

CREATE TABLE_A
(pk INTEGER, FIELD_A integer)

CREATE TABLE_B
(pk INTEGER, FIELD_B integer)

SELECT TABLE_A.PK, FIELD_A FROM TABLE_A LEFT JOIN TABLE_B ON TABLE_A.PK = TABLE_B.PK

//According to performance analysis TABLE_B is queried even though clearly no fields are required from it and because it's left joined it can't affect the rows returned from table_A. You may think big deal serve the silly DBA for writing such a stupid query, but consider:

CREATE VIEW VIEW_B
AS
SELECT TABLE_A.PK as PK, FIELD_A, FIELD_B FROM TABLE_A LEFT JOIN TABLE_B ON TABLE_A.PK = TABLE_B.PK

SELECT FIELD_A from VIEW_B

Not so silly now, but inefficient as TABLE_B is unnecessarily queried (according to my analyzer). Can't we detect that TABLE_B is not actually required at all and optimize it out?
Unless my query performance analyzer is crap or I am being a loony, I think there is scope for greater efficiency by ignoring redundant tables which are outer joined:

Consider the following:

CREATE TABLE_A
(pk INTEGER, FIELD_A integer)

CREATE TABLE_B
(pk INTEGER, FIELD_B integer)

ALTER TABLE TABLE_A ADD PRIMARY KEY (PK);
ALTER TABLE TABLE_B ADD PRIMARY KEY (PK);

ALTER TABLE TABLE_B ADD FOREIGN KEY (PK) REFERENCES TABLE_A(PK);

SELECT TABLE_A.PK, FIELD_A FROM TABLE_A LEFT JOIN TABLE_B ON TABLE_A.PK = TABLE_B.PK

//According to performance analysis TABLE_B's index is queried even though clearly no fields are required from it and because it's left joined it can't affect the rows returned from table_A. The weird thing is that if the index doesn't exist then TABLE_B is optimized out! You may think big deal serve the silly DBA for writing such a stupid query, but consider:

CREATE VIEW VIEW_B
AS
SELECT TABLE_A.PK as PK, FIELD_A, FIELD_B FROM TABLE_A LEFT JOIN TABLE_B ON TABLE_A.PK = TABLE_B.PK

SELECT FIELD_A from VIEW_B

Not so silly now, but inefficient as TABLE_B is unnecessarily queried (according to my analyzer). Can't we detect that TABLE_B is not actually required at all and optimize it out?
Tim Kelly made changes - 18/Jun/08 06:00 PM
Description Unless my query performance analyzer is crap or I am being a loony, I think there is scope for greater efficiency by ignoring redundant tables which are outer joined:

Consider the following:

CREATE TABLE_A
(pk INTEGER, FIELD_A integer)

CREATE TABLE_B
(pk INTEGER, FIELD_B integer)

ALTER TABLE TABLE_A ADD PRIMARY KEY (PK);
ALTER TABLE TABLE_B ADD PRIMARY KEY (PK);

ALTER TABLE TABLE_B ADD FOREIGN KEY (PK) REFERENCES TABLE_A(PK);

SELECT TABLE_A.PK, FIELD_A FROM TABLE_A LEFT JOIN TABLE_B ON TABLE_A.PK = TABLE_B.PK

//According to performance analysis TABLE_B's index is queried even though clearly no fields are required from it and because it's left joined it can't affect the rows returned from table_A. The weird thing is that if the index doesn't exist then TABLE_B is optimized out! You may think big deal serve the silly DBA for writing such a stupid query, but consider:

CREATE VIEW VIEW_B
AS
SELECT TABLE_A.PK as PK, FIELD_A, FIELD_B FROM TABLE_A LEFT JOIN TABLE_B ON TABLE_A.PK = TABLE_B.PK

SELECT FIELD_A from VIEW_B

Not so silly now, but inefficient as TABLE_B is unnecessarily queried (according to my analyzer). Can't we detect that TABLE_B is not actually required at all and optimize it out?
Unless my query performance analyzer is crap or I am being a loony, I think there is scope for greater efficiency by ignoring redundant tables which are outer joined:

Consider the following:

CREATE TABLE_A
(pk INTEGER, FIELD_A integer)

CREATE TABLE_B
(pk INTEGER, FIELD_B integer)

ALTER TABLE TABLE_A ADD PRIMARY KEY (PK);
ALTER TABLE TABLE_B ADD PRIMARY KEY (PK);

ALTER TABLE TABLE_B ADD FOREIGN KEY (PK) REFERENCES TABLE_A(PK);

SELECT TABLE_A.PK, FIELD_A FROM TABLE_A LEFT JOIN TABLE_B ON TABLE_A.PK = TABLE_B.PK

//According to performance analysis TABLE_B's index is queried even though clearly no fields are required from it and because it's left joined it can't affect the rows returned from table_A. The weird thing is that if the index doesn't exist then TABLE_B is optimized out! You may think big deal serve the silly DBA for writing such a stupid query, but consider:

CREATE VIEW VIEW_B
AS
SELECT TABLE_A.PK as PK, FIELD_A, FIELD_B FROM TABLE_A LEFT JOIN TABLE_B ON TABLE_A.PK = TABLE_B.PK

SELECT FIELD_A from VIEW_B

Not so silly now, but inefficient as TABLE_B's index is unnecessarily fetched (according to my analyzer). Can't we detect that TABLE_B is not actually required at all and optimize it out?