Informix - Problem description
Problem IT29102 | Status: Closed |
IF XYUNITS IN A SPATIAL REFERENCE SYSTEM IS INCREASED WHEN DATA DISTRIBUTIONS EXIST, OPTIMIZER MAY FAVOR SPATIAL INDEX TOO MUCH | |
product: | |
INFORMIX SERVER / 5725A3900 / B70 - IDS 11.70 | |
Problem description: | |
So after fixing APAR IT27689, which dealt with distributions not getting built correctly when the xyunits of a spatial reference system was increase past a certain point, it seems the selectivity of spatial filters may be too small now. This then causes the optimizer to favor paths using the rtree index on the spatial column which may result in more work in the server and slower query times. If the distributions are dropped, a new, faster/less work plan could be picked. It would not be expected that a better query plan be generated with less information available (like having the distributions on the spatial column not exist). For example in the query statistics part of set explain output lets look at the following 2 outputs, 1 with distributions on the spatial column and the next without. With distributions: Query statistics: ----------------- Table map : ---------------------------- Internal name Table name ---------------------------- t1 ec t2 se type table rows_prod est_rows rows_scan time est_cost ---------------------------------------------------------------- --- scan t1 26241 1 26241 00:00.19 1 type table rows_prod est_rows rows_scan time est_cost ---------------------------------------------------------------- --- scan t2 3 567 3 00:00.08 0 type rows_prod est_rows time est_cost ------------------------------------------------- nljoin 3 1 00:00.28 4 t1 is the table with spatial and the scan showed it was using the rtree...this plan causes 26241 rows to be produced to be nested loop joined to t2. The overall time of the query is ~ 0.3 seconds Without distributions on spatial column: Query statistics: ----------------- Table map : ---------------------------- Internal name Table name ---------------------------- t1 se t2 ec type table rows_prod est_rows rows_scan time est_cost ---------------------------------------------------------------- --- scan t1 567 567 567 00:00.00 33 type table rows_prod est_rows rows_scan time est_cost ---------------------------------------------------------------- --- scan t2 3 565006 567 00:00.00 2 type rows_prod est_rows time est_cost ------------------------------------------------- nljoin 3 57 00:00.00 1083 This query scans the smaller table 1st and joins to the table with the spatial column using a different index and applies the spatial filter as a table level (so after the join has happened). This query scans fewer rows and is taking so little time that the output shows the time to run is 0.0 seconds. This small difference can be huge on busy systems that run the affected queries heavily. | |
Problem Summary: | |
**************************************************************** * USERS AFFECTED: * * Users of IDS versions prior to 12.10.xC14. * **************************************************************** * PROBLEM DESCRIPTION: * * If xyunits in a spatial reference system is increased, when * * data distributions are added, the optimizer may favor the * * spatial index too much. * **************************************************************** * RECOMMENDATION: * **************************************************************** | |
Local Fix: | |
Solution | |
Workaround | |
not known / see Local fix | |
Timestamps | |
Date - problem reported : Date - problem closed : Date - last modified : | 10.05.2019 23.09.2019 19.02.2020 |
Problem solved at the following versions (IBM BugInfos) | |
12.10.xC14 | |
Problem solved according to the fixlist(s) of the following version(s) |