RE: [firebird-support] V1.56 query killing my V2.54 app
Ok, used the +0 and worked. On v1.56 I was used with setting up a high granularity data column (col04Int - part of the primary key) with a True/false (0/1) type of column (ColDetSmIntFlag) to boost the selectivity of the index IXColDetSmIntFlag. I kept the index with that configuration for a just in case. (The stat of the index is 0,01407...) Set, don't get me wrong, I am very gratefull for your help and for Firebird, but saying that a Natural on a big table seems better than an index doesn't compute for me, and I've been using Firebird since Interbase and Oracle since v6 (as DBA BTW). At a least case scenario it should use the PK when there is a declared join using the PK. For me, the new optimizer is wierd and highly illogical. Glad to hear that +0 worked. We used Fb 1.5 for a long time (switched from 1.5 to 2.5 a year or two ago) and I’m happy to say that my impression generally is that the optimizer in 2.5 is better than it was in 1.5 and that the need for tricks like +0 is less than it used to be. Though there will of course be differences, and it is not a surprise that you notice some drawbacks rather than benefits when upgrading a particular database (I assume you have optimized poorly performing queries with Fb 1.5 long ago, rather than been waiting for the upgrade). If you compare new, unoptimized queries between 1.5 and 2.5, odds are that you would conclude that 2.5 are better. I guess the reason for the optimizer preferring the NATURAL plan is a combination of msttbl having about half the number of records of dettbl and the index on ColDetSmIntFlag having lousy selectivity (the optimizer doesn’t have histograms yet (I am uncertain whether histograms are part of Firebird 3 or if it will be a later release), and doesn’t know that 1 is a less frequent value than 0). Set
Re: [firebird-support] V1.56 query killing my V2.54 app
On 07 Apr 2015 08:51:22 -0700, andrew_s_...@yahoo.com [firebird-support] firebird-support@yahoogroups.com wrote: Hi all, I'm trying to upgrade an app to v2.54 from v1.56 but there are some queries that aren't planning the way they should. I've been changing the query and trying to change indexes to no success. Is there a way to force FB 2.54 to chose the right indexes? Did you update the index statistics? Maybe your statistics are out of date. Mark
Re: [firebird-support] V1.56 query killing my V2.54 app
Hi all, I'm trying to upgrade an app to v2.54 from v1.56 but there are some queries that aren't planning the way they should. I have this query: Select Distinct a.col01Int, a.col02SmInt, a.col03SmInt, a.col04VarCh45 from dettbl b Inner Join msttbl a on (a.col01Int = i.col01Int and a.col02SmInt = i.col02SmInt and a.col03SmInt = i.col03SmInt) where b.ColDetSmIntFlag = 1 Order by a.col04VarCh45 on v1.56 I get this plan: PLAN SORT (SORT (JOIN (B INDEX (IXColDetSmIntFlag),A INDEX (PK_msttbl and on v2.54 I get PLAN SORT (SORT (JOIN (A NATURAL, B INDEX (FK_dettbl that is killing my app. The tables in question have 450K+ and 800K+ rows. ALTER TABLE msttbl ADD CONSTRAINT PK_msttbl PRIMARY KEY (col01Int, col02Int, col03Int); CREATE INDEX IXmsttbl_col04VarCh45 ON msttbl (col04VarCh45); ALTER TABLE dettbl ADD CONSTRAINT PK_dettbl PRIMARY KEY col01Int, col02Int, col03Int, col04Int); ALTER TABLE dettbl ADD CONSTRAINT FK_dettbl FOREIGN KEY (col01Int, col02Int, col03Int) REFERENCES msttbl (col01Int, col02Int, col03Int); CREATE INDEX IXColDetSmIntFlag ON dettbl (ColDetSmIntFlag, col04Int); I've been changing the query and trying to change indexes to no success. Is there a way to force FB 2.54 to chose the right indexes? Hi Andrew! Forcing a correct plan is not always benefitial or possible, but it is normally quite easy to prevent a bad plan from being used. I'd recommend you to try: Select Distinct a.col01Int, a.col02SmInt, a.col03SmInt, a.col04VarCh45 from dettbl b Inner Join msttbl a on (a.col01Int = b.col01Int+0 and a.col02SmInt = b.col02SmInt and a.col03SmInt = b.col03SmInt) where b.ColDetSmIntFlag = 1 Order by a.col04VarCh45 The additional +0 should prevent FK_dettbl from being used, but there's no guarantee that another poor plan cannot be chosen, so this kind of modification sometimes needs a couple of attempts before you reach a good result. HTH, Set
Re: [firebird-support] V1.56 query killing my V2.54 app
Hello Set, Actually, the problem isn't with FK_dettbl, but with the A Natural. In a Master detail relationship with 450K+ rows in the master and 800K+ rows in the detail, that natural is a killer. Thanks Again
Re: [firebird-support] V1.56 query killing my V2.54 app
Actually, the problem isn't with FK_dettbl, but with the A Natural. In a Master detail relationship with 450K+ rows in the master and 800K+ rows in the detail, that natural is a killer. The problem is that Firebird thinks that PLAN SORT (SORT (JOIN (A NATURAL, B INDEX (FK_dettbl is a better choice than PLAN SORT (SORT (JOIN (B INDEX (IXColDetSmIntFlag),A INDEX (PK_msttbl The optimizer doesn't think NATURAL is better than using an index, it thinks that the selectivity of FK_dettbl is so much better than IXColDetSmIntFlag and PK_msttbl combined that it outweights the cost of going NATURAL on A. Normally, it isn't all too bad to go natural on the first table of a plan, but b.ColDetSmIntFlag = 1 may be more selective than the optimizer thinks and then preventing a particular plan from being used can help speed up queries like yours. The +0 should prevent FK_dettbl from being used, and I'm pretty sure the optimizer will not try to go natural on both tables. Hence the optimizer should suggest another plan - hopefully the one you want. HTH, Set
Re: [firebird-support] V1.56 query killing my V2.54 app
Ok, used the +0 and worked. On v1.56 I was used with setting up a high granularity data column (col04Int - part of the primary key) with a True/false (0/1) type of column (ColDetSmIntFlag) to boost the selectivity of the index IXColDetSmIntFlag. I kept the index with that configuration for a just in case. (The stat of the index is 0,01407...) Set, don't get me wrong, I am very gratefull for your help and for Firebird, but saying that a Natural on a big table seems better than an index doesn't compute for me, and I've been using Firebird since Interbase and Oracle since v6 (as DBA BTW). At a least case scenario it should use the PK when there is a declared join using the PK. For me, the new optimizer is wierd and highly illogical. Thanks Andrew
