Issue Details (XML | Word | Printable)

Key: CORE-2835
Type: Bug Bug
Status: Closed Closed
Resolution: Fixed
Priority: Major Major
Assignee: Dmitry Yemanov
Reporter: Doychin Bondzhev
Votes: 0
Watchers: 1
Operations

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

Natural is used to select instead of primary key index

Created: 29/Jan/10 04:59 PM   Updated: 14/Feb/11 11:50 AM
Component/s: Engine
Affects Version/s: 2.5 RC1, 2.5.0
Fix Version/s: 2.5.1

Time Tracking:
Not Specified

Environment: Windows 7 64bit and FB 64 bit

Planning Status: Unspecified


 Description  « Hide
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);



 All   Comments   Work Log   Change History   Version Control   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Sean Leyne added a comment - 29/Jan/10 10:44 PM
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 A.ID = B.FK...) and not the prefered SQL-92 syntax (SELECT ... FROM A JOIN B ON B.FK = A.ID JOIN C ... WHERE ...)? If you change the query syntax, does the PLAN change?

Doychin Bondzhev added a comment - 29/Jan/10 11:57 PM
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.

Sean Leyne added a comment - 30/Jan/10 06:21 PM
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).

Doychin Bondzhev added a comment - 30/Jan/10 08:15 PM
After inserting some data plan did not change. I had to recompute index selectivity and plan is now OK.

Doychin Bondzhev added a comment - 30/Jan/10 08:18 PM
I also removed all data from NET_DEV_INTERCONNECTION and recomputed indexes again and plan did not return back to first form.

Dmitry Yemanov added a comment - 02/Feb/10 01:14 PM
So, should we conclude that the issue was caused by the outdated index statistics? Or did I miss something in the discussion?

Doychin Bondzhev added a comment - 02/Feb/10 01:53 PM
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.

Dmitry Yemanov added a comment - 02/Feb/10 02:01 PM
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?

Doychin Bondzhev added a comment - 02/Feb/10 02:31 PM
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.


Dmitry Yemanov added a comment - 03/Feb/10 05:46 AM
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)?

Doychin Bondzhev added a comment - 03/Feb/10 12:17 PM
Sorry I missed that one.

"FK_NET_DEV_INTERCONNECTION_001","PRIM_DEVID",0,1

Dmitry Yemanov added a comment - 16/Feb/10 03:49 PM
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.

Doychin Bondzhev added a comment - 16/Feb/10 04:36 PM
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.

Dmitry Yemanov added a comment - 16/Feb/10 04:43 PM
firebird2 at yandex dot ru