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-automations opened this issue Jan 29, 2010 · 21 comments
Closed

Comments

@firebird-automations
Copy link
Collaborator

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-automations
Copy link
Collaborator Author

Modified by: @dyemanov

assignee: Dmitry Yemanov [ dimitr ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

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

@firebird-automations
Copy link
Collaborator Author

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-automations
Copy link
Collaborator Author

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-automations
Copy link
Collaborator Author

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-automations
Copy link
Collaborator Author

Commented by: @doychin

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

@firebird-automations
Copy link
Collaborator Author

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-automations
Copy link
Collaborator Author

Modified by: @dyemanov

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

@firebird-automations
Copy link
Collaborator Author

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-automations
Copy link
Collaborator Author

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-automations
Copy link
Collaborator Author

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-automations
Copy link
Collaborator Author

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-automations
Copy link
Collaborator Author

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-automations
Copy link
Collaborator Author

Commented by: @doychin

Sorry I missed that one.

"FK_NET_DEV_INTERCONNECTION_001","PRIM_DEVID",0,1

@firebird-automations
Copy link
Collaborator Author

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-automations
Copy link
Collaborator Author

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-automations
Copy link
Collaborator Author

Commented by: @dyemanov

firebird2 at yandex dot ru

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

Version: 2.5.0 [ 10221 ]

Fix Version: 2.5.1 [ 10333 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

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

resolution: Fixed [ 1 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pcisar

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

@firebird-automations
Copy link
Collaborator Author

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
Development

No branches or pull requests

2 participants