|
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. 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). After inserting some data plan did not change. I had to recompute index selectivity and plan is now OK.
I also removed all data from NET_DEV_INTERCONNECTION and recomputed indexes again and plan did not return back to first form.
So, should we conclude that the issue was caused by the outdated index statistics? Or did I miss something in the discussion?
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. 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?
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. 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)? Sorry I missed that one.
"FK_NET_DEV_INTERCONNECTION_001","PRIM_DEVID",0,1 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. 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.
firebird2 at yandex dot ru
|
|||||||||||||||||||||||||||||||||||||||||||||||||||
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?