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
There are no subversion log entries for this issue yet.