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

Natural is used to select instead of primary key index [CORE2835] #3221

Closed
firebird-issue-importer opened this issue Jan 29, 2010 · 21 comments
Closed

Comments

@firebird-issue-importer

Submitted by: @doychin

Using this Select:
SELECT DISTINCT t0_dep.ID
FROM NET_NET_DEVICE t1_nd, NET_NET_DEVICE t0_dep, NET_DEV_INTERCONNECTION t3_nd_dependantDevices_RELATION
WHERE ((t1_nd.ID = ?)) AND t1_nd.ID=t3_nd_dependantDevices_RELATION.PRIM_DEVID
AND t0_dep.ID=t3_nd_dependantDevices_RELATION.SECONDARY_DEVID

in FB 2.1.2 it produces this plan:
PLAN SORT (JOIN (T1_ND INDEX (PK_NET_NET_DEVICE), T3_ND_DEPENDANTDEVICES_RELATION INDEX (FK_NET_DEV_INTERCONNECTION_001), T0_DEP INDEX (PK_NET_NET_DEVICE)))

but In 2.5.RC1 it uses another plan:
PLAN SORT (JOIN (T0_DEP NATURAL, T3_ND_DEPENDANTDEVICES_RELATION INDEX (PK_NET_DEV_INTERCONNECTION), T1_ND INDEX (PK_NET_NET_DEVICE)))

As you can see selecting T0_DEP natural is used instead of PK_NET_NET_DEVICE

The tables definitions are below:

CREATE TABLE NET_NET_DEVICE
(
ID INTEGER NOT NULL,
CONSTRAINT PK_NET_NET_DEVICE PRIMARY KEY (ID)
);

CREATE TABLE NET_DEV_INTERCONNECTION
(
PRIM_DEVID INTEGER NOT NULL,
SECONDARY_DEVID INTEGER NOT NULL,
INTERCONNECT_LEVEL INTEGER,
CONSTRAINT PK_NET_DEV_INTERCONNECTION PRIMARY KEY (PRIM_DEVID, SECONDARY_DEVID)
);

ALTER TABLE NET_DEV_INTERCONNECTION ADD CONSTRAINT FK_NET_DEV_INTERCONNECTION_001
FOREIGN KEY (PRIM_DEVID) REFERENCES NET_NET_DEVICE
(ID);

ALTER TABLE NET_DEV_INTERCONNECTION ADD CONSTRAINT FK_NET_DEV_INTERCONNECTION_002
FOREIGN KEY (SECONDARY_DEVID) REFERENCES NET_NET_DEVICE
(ID);

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Jan 29, 2010

Modified by: @dyemanov

assignee: Dmitry Yemanov [ dimitr ]

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Jan 29, 2010

Modified by: @dyemanov

status: Open [ 1 ] => In Progress [ 3 ]

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Jan 30, 2010

Commented by: Sean Leyne (seanleyne)

Does the PLAN reflect tables with real data, or is the PLAN simply based on an empty database?

Are you aware that you are using the SQL-87 syntax for joins (i.e. SELECT ... FROM A, B, C WHERE http://A.ID = http://B.FK...) and not the prefered SQL-92 syntax (SELECT ... FROM A JOIN B ON http://B.FK = http://A.ID JOIN C ... WHERE ...)? If you change the query syntax, does the PLAN change?

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Jan 30, 2010

Commented by: @doychin

On the real database where I first noticed this difference table NET_DEV_INTERCONNECTION is empty. The other table contains records.

Query form is out of my control. It is generated by JBoss EJB code.

All Sql code that I write and use directly uses the new syntax but queries generated by application server still use older syntax.

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Jan 30, 2010

Commented by: Sean Leyne (seanleyne)

Too bad you can't control the SQL...

If records exist in the NET_DEV_INTERCONNECTION table, does the PLAN change? (I suspect that this is causing the optimizer to create a 'short-circuited' PLAN).

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Jan 30, 2010

Commented by: @doychin

After inserting some data plan did not change. I had to recompute index selectivity and plan is now OK.

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Jan 30, 2010

Commented by: @doychin

I also removed all data from NET_DEV_INTERCONNECTION and recomputed indexes again and plan did not return back to first form.

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Feb 2, 2010

Modified by: @dyemanov

status: In Progress [ 3 ] => Open [ 1 ]

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Feb 2, 2010

Commented by: @dyemanov

So, should we conclude that the issue was caused by the outdated index statistics? Or did I miss something in the discussion?

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Feb 2, 2010

Commented by: @doychin

I can tell only that this database was just restored from backup made with FB 2.1.2

If indexes are outdated on a fresh restore then I think there is a problem.

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Feb 2, 2010

Commented by: @dyemanov

OK. Could you please post here the number of records in all these tables and selectivity of all the indices mentioned in both (good and bad) plans?

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Feb 2, 2010

Commented by: @doychin

NET_NET_DEVICE - 8636 records
NET_DEV_INTERCONNECTION - 4029 records

PLAN SORT (JOIN (T0_DEP NATURAL, T3_ND_DEPENDANTDEVICES_RELATION INDEX (PK_NET_DEV_INTERCONNECTION), T1_ND INDEX (PK_NET_NET_DEVICE)))

"PK_NET_DEV_INTERCONNECTION","PRIM_DEVID",0,1
"PK_NET_DEV_INTERCONNECTION","SECONDARY_DEVID",1,0,000248200551141053

"PK_NET_NET_DEVICE","ID",0,0,000115794347948395

Without changing anything on the database just by recomputing index selectivity plan is still the same.

I think last time I did testing on this I made a change to data in NET_DEV_INTERCONNECTION and for that reason the plan was changed.

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Feb 3, 2010

Commented by: @dyemanov

And what's selectivity of FK_NET_DEV_INTERCONNECTION_001?
Finally, would you mind posting the execution statistics (time and number of page fetches) with both these plans (on the same data)?

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Feb 3, 2010

Commented by: @doychin

Sorry I missed that one.

"FK_NET_DEV_INTERCONNECTION_001","PRIM_DEVID",0,1

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Feb 16, 2010

Commented by: @dyemanov

I cannot reproduce the issue with the provided information:

execute block
as
declare cnt1 int = 8636;
declare cnt2 int = 4029;
begin
delete from NET_DEV_INTERCONNECTION;
delete from NET_NET_DEVICE;

while (cnt1 > 0) do
begin
insert into NET_NET_DEVICE values (:cnt1);
cnt1 = cnt1 - 1;
end

while (cnt2 > 0) do
begin
insert into NET_DEV_INTERCONNECTION values (1, :cnt2, null);
cnt2 = cnt2 - 1;
end

execute statement 'set statistics index PK_NET_NET_DEVICE';
execute statement 'set statistics index PK_NET_DEV_INTERCONNECTION';
execute statement 'set statistics index FK_NET_DEV_INTERCONNECTION_001';
execute statement 'set statistics index FK_NET_DEV_INTERCONNECTION_002';
end

commit

select distinct t0_dep.id
from net_net_device t1_nd,
net_net_device t0_dep,
net_dev_interconnection t3_nd_dependantdevices_relation
where ((t1_nd.id = :a))
and t1_nd.id=t3_nd_dependantdevices_relation.prim_devid
and t0_dep.id=t3_nd_dependantdevices_relation.secondary_devid

PLAN SORT (JOIN (T1_ND INDEX (PK_NET_NET_DEVICE), T3_ND_DEPENDANTDEVICES_RELATION INDEX (FK_NET_DEV_INTERCONNECTION_001), T0_DEP INDEX (PK_NET_NET_DEVICE)))

Tested with v2.5 RC2.

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Feb 16, 2010

Commented by: @doychin

Where I can send you an email with backup of database that produces this plan. I just did a backup and restore on FB 2.5Rc2 and again I get the same plan.

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Feb 16, 2010

Commented by: @dyemanov

firebird2 at yandex dot ru

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Feb 14, 2011

Modified by: @dyemanov

Version: 2.5.0 [ 10221 ]

Fix Version: 2.5.1 [ 10333 ]

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Feb 14, 2011

Modified by: @dyemanov

status: Open [ 1 ] => Resolved [ 5 ]

resolution: Fixed [ 1 ]

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Feb 14, 2011

Modified by: @pcisar

status: Resolved [ 5 ] => Closed [ 6 ]

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented May 27, 2015

Modified by: @pavel-zotov

status: Closed [ 6 ] => Closed [ 6 ]

QA Status: Done successfully

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Linked pull requests

Successfully merging a pull request may close this issue.

None yet
2 participants