| Subject | Re: [firebird-support] Upgrading Firebird 1.54 to 2.03 - Query plan |
|---|---|
| Author | Helen Borrie |
| Post date | 2007-12-12T00:55:05Z |
At 04:52 AM 12/12/2007, you wrote:
[Also, although it's not applicable here, for any query that has both inner and outer joins, a quirk of the optimizer makes it important that any inner joins precede outer joins and, in some cases, that left joins precede right or full joins..]
Now, let's rewrite this SQL89 query using explicit join syntax:
select J.JOB_ID,
J.JOB_NUMBER,
C.SHORT_NAME as CUSTOMER_NAME,
B.BRANCH_NAME,
S.DESCRIPTION as JOB_STATUS,
J.CUST_REF_NO
from JOB J
join CUSTOMER C on C.CUSTOMER_ID = J.CUSTOMER_ID
join BRANCH B on B.BRANCH_ID = J.BRANCH_ID
join JOBSTATUS S on S.JOB_STATUS_CODE = J.JOB_STATUS_CODE
where J.JOB_ID STARTING WITH '544'
order by J.JOB_ID DESCENDING
The plan you like, from Fb 1.5.4 is this one:
PLAN JOIN (J ORDER IX_JOB_ID_DESC,C INDEX (RDB$PRIMARY66),B INDEX
(RDB$PRIMARY73),S INDEX (RDB$PRIMARY62))
It's quite nice, because it's able to use the descending index to subvert the natural order of the table and get direct hits on the WHERE criterion. After that, all it has left to do is read the join criteria on the found records and pick up the lookup data by matching. Because its set at this point is already in the requested order, it has nothing further to do. The boot might be on the other foot if Job is a very large table and the WHERE clause requests Job_IDs in a much lower range or requests a variety of ranges. Depending on the index statistics at the time, it might choose the descending index, where Job's PK index (or natural order) would be a better choice.
Fb 2.0 does this with your original query:
PLAN SORT (JOIN (C NATURAL, J INDEX (RDB$FOREIGN106), S INDEX
(RDB$PRIMARY62), B INDEX (RDB$PRIMARY73)))
It has made multiple bad choices here, if " starting with '544' " would find the requested job_ids near the end of the set that emerges from the match between Customer and Job's FK to it. AFAICT, its use of the FK index to sort the Customer_Ids blocks it from using the descending index to limit the initial stream. It subsequently does two more sorts on that output stream, the first to get the job_status.description as a derived field, the second to get the branch_name. Nowhere for that descending index to go....
The first thing to do, if you have not done it so far, is to recalculate the index statistics. This is very important, since Fb 2.0 stores, manages and uses these statistics quite differently from Fb 1.5. (If you migrated the database by restoring a Fb 1.5 backup under Fb 2.0's gbak, then this will have been done for you. You still need to schedule in periodic housekeeping on the indexes if you're not doing a regular backup/restore cycle.)
If you are still running your database as ODS 10.n, the Fb 2 index optimizations could work against you under some conditions, e.g., if your constraint indexes and/or other indexes are composite or the customer FK on job has a consistent pattern of "most jobs being for a very small number of customers" without attention having been paid to index balancing.
In any event, SET STATISTICS indexname will recompute the statistics for use *next time the database is opened*, i.e., its effect is deferred if there are active connections. Resetting the statistics should at least help the optimizer to make more judicious choices. Rebuilding the indexes will both rebalance them and reset the statistics (for regular indexes, set index blah inactive/set index blah active; for constraint indexes, dropping and recreating the constraints...all of this done while there are no users attached, natch!!)
As the next step, rewrite the query just as I've shown above and inspect the plan. Depending on how far along the migration route you have gone, you should get either an optimal plan or at least a better one. It might not be the same as the Fb 1.5 plan, since the improved decision strategy of Fb 2.0 has paths available to it that Fb 1.5 didn't have.
The specified order of explicit inner joins shouldn't matter...but, as long as you are playing about with this query, it won't do any harm to vary the specified order and see whether it affects the plan and performance. It wouldn't hurt to experiment with using correlated subqueries instead of joins for those lookups, i.e.,
select J.JOB_ID,
J.JOB_NUMBER,
(select C.SHORT_NAME from Customer C
where C.Customer_id = J.Customer_id) as CUSTOMER_NAME,
(select B.BRANCH_NAME from Branch B
where B.BRANCH_ID = J.BRANCH_ID) as BRANCH_NAME,
(select S.DESCRIPTION from Job_status S
where S.JOB_STATUS_CODE = J.JOB_STATUS_CODE) as JOB_STATUS,
J.CUST_REF_NO
from JOB J
where J.JOB_ID STARTING WITH '544'
order by J.JOB_ID DESCENDING
On the other hand, if the database isn't ODS 11, some paths might be unavailable, which might lead to a wrong second choice. It would be interesting to see what does happen...
./heLen
>I am currently evaluating the process of upgrading from Firebird 1.54First is your use of the ancient SQL89 join syntax: the longer you neglect this (in terms of stepping up through engine versions) the worse it will become. However, Woody's sample, though useful for reference, is a bit misleading for this case. You would never replace an inner join with an outer join if you were seeking to get the same output set or the "best" plan according with your v.1.5.4 experience.
>to 2.03 and have hit a problem. Having saved a 1.54 database and
>restored it to 2.03 everything so far seems to work well except the
>following query:
>
>select J.JOB_ID,
> J.JOB_NUMBER,
> C.SHORT_NAME as CUSTOMER_NAME,
> B.BRANCH_NAME,
> S.DESCRIPTION as JOB_STATUS,
> J.CUST_REF_NO
> from JOB J,
> CUSTOMER C,
> BRANCH B,
> JOBSTATUS S
> where J.JOB_ID LIKE '544%'
> and C.CUSTOMER_ID = J.CUSTOMER_ID
> and B.BRANCH_ID = J.BRANCH_ID
> and S.JOB_STATUS_CODE = J.JOB_STATUS_CODE
> order by J.JOB_ID DESCENDING
>
>JOB table has the following index:
>CREATE DESCENDING INDEX IX_JOB_ID_DESC ON JOB(JOB_ID);
>
>When executing this query, Firebird 1.54 uses the following plan:
>PLAN JOIN (J ORDER IX_JOB_ID_DESC,C INDEX (RDB$PRIMARY66),B INDEX
>(RDB$PRIMARY73),S INDEX (RDB$PRIMARY62))
>while Firebird 2.03 uses a different plan, as follows:
>PLAN SORT (JOIN (C NATURAL, J INDEX (RDB$FOREIGN106), S INDEX
>(RDB$PRIMARY62), B INDEX (RDB$PRIMARY73)))
>The result is that this query runs very slow on the Firebird 2.03
>database because the IX_JOB_ID_DESC index was not utilised in the
>plan.
>
>Can anyone shed any light on why this might be happening?
[Also, although it's not applicable here, for any query that has both inner and outer joins, a quirk of the optimizer makes it important that any inner joins precede outer joins and, in some cases, that left joins precede right or full joins..]
Now, let's rewrite this SQL89 query using explicit join syntax:
select J.JOB_ID,
J.JOB_NUMBER,
C.SHORT_NAME as CUSTOMER_NAME,
B.BRANCH_NAME,
S.DESCRIPTION as JOB_STATUS,
J.CUST_REF_NO
from JOB J
join CUSTOMER C on C.CUSTOMER_ID = J.CUSTOMER_ID
join BRANCH B on B.BRANCH_ID = J.BRANCH_ID
join JOBSTATUS S on S.JOB_STATUS_CODE = J.JOB_STATUS_CODE
where J.JOB_ID STARTING WITH '544'
order by J.JOB_ID DESCENDING
The plan you like, from Fb 1.5.4 is this one:
PLAN JOIN (J ORDER IX_JOB_ID_DESC,C INDEX (RDB$PRIMARY66),B INDEX
(RDB$PRIMARY73),S INDEX (RDB$PRIMARY62))
It's quite nice, because it's able to use the descending index to subvert the natural order of the table and get direct hits on the WHERE criterion. After that, all it has left to do is read the join criteria on the found records and pick up the lookup data by matching. Because its set at this point is already in the requested order, it has nothing further to do. The boot might be on the other foot if Job is a very large table and the WHERE clause requests Job_IDs in a much lower range or requests a variety of ranges. Depending on the index statistics at the time, it might choose the descending index, where Job's PK index (or natural order) would be a better choice.
Fb 2.0 does this with your original query:
PLAN SORT (JOIN (C NATURAL, J INDEX (RDB$FOREIGN106), S INDEX
(RDB$PRIMARY62), B INDEX (RDB$PRIMARY73)))
It has made multiple bad choices here, if " starting with '544' " would find the requested job_ids near the end of the set that emerges from the match between Customer and Job's FK to it. AFAICT, its use of the FK index to sort the Customer_Ids blocks it from using the descending index to limit the initial stream. It subsequently does two more sorts on that output stream, the first to get the job_status.description as a derived field, the second to get the branch_name. Nowhere for that descending index to go....
The first thing to do, if you have not done it so far, is to recalculate the index statistics. This is very important, since Fb 2.0 stores, manages and uses these statistics quite differently from Fb 1.5. (If you migrated the database by restoring a Fb 1.5 backup under Fb 2.0's gbak, then this will have been done for you. You still need to schedule in periodic housekeeping on the indexes if you're not doing a regular backup/restore cycle.)
If you are still running your database as ODS 10.n, the Fb 2 index optimizations could work against you under some conditions, e.g., if your constraint indexes and/or other indexes are composite or the customer FK on job has a consistent pattern of "most jobs being for a very small number of customers" without attention having been paid to index balancing.
In any event, SET STATISTICS indexname will recompute the statistics for use *next time the database is opened*, i.e., its effect is deferred if there are active connections. Resetting the statistics should at least help the optimizer to make more judicious choices. Rebuilding the indexes will both rebalance them and reset the statistics (for regular indexes, set index blah inactive/set index blah active; for constraint indexes, dropping and recreating the constraints...all of this done while there are no users attached, natch!!)
As the next step, rewrite the query just as I've shown above and inspect the plan. Depending on how far along the migration route you have gone, you should get either an optimal plan or at least a better one. It might not be the same as the Fb 1.5 plan, since the improved decision strategy of Fb 2.0 has paths available to it that Fb 1.5 didn't have.
The specified order of explicit inner joins shouldn't matter...but, as long as you are playing about with this query, it won't do any harm to vary the specified order and see whether it affects the plan and performance. It wouldn't hurt to experiment with using correlated subqueries instead of joins for those lookups, i.e.,
select J.JOB_ID,
J.JOB_NUMBER,
(select C.SHORT_NAME from Customer C
where C.Customer_id = J.Customer_id) as CUSTOMER_NAME,
(select B.BRANCH_NAME from Branch B
where B.BRANCH_ID = J.BRANCH_ID) as BRANCH_NAME,
(select S.DESCRIPTION from Job_status S
where S.JOB_STATUS_CODE = J.JOB_STATUS_CODE) as JOB_STATUS,
J.CUST_REF_NO
from JOB J
where J.JOB_ID STARTING WITH '544'
order by J.JOB_ID DESCENDING
On the other hand, if the database isn't ODS 11, some paths might be unavailable, which might lead to a wrong second choice. It would be interesting to see what does happen...
./heLen