Wrong column statistics for very large tables
Details
| Detail name | Value |
|---|---|
| Changelog Number | 5066 |
| Type | Bug |
| Status | Resolved |
| Affected Versions | EXASOL 6.0.0 |
| Fix Versions | EXASOL 6.0.5 |
| Resolution Date | 2017-11-02 |
Situation
Column statistics (distinct estimates) are required for the join order optimizer to minimize pipeline costs. Those statistics are computed on demand (joins, filters) and maintained as part of DML statements. They are recomputed if a significant amount of data has changed since the last computation.
For very large tables, having more than 10.213 segments (-EXASOL-1868-) or more than about 43 billion rows (including deleted marked rows), those statistics are not computed correctly. As a consequence, queries that rely heavily on those figures might end up choosing a suboptimal join queue and in consequence take much longer to compute due to this bug.
How to reproduce
You can find out which tables are affected by this bug using the following statement
select table_schema,table_name,count(*) from "$EXA_TABLE_SEGMENTS" group by 1,2 having count(*) > 10213;
* Please note that hidden ("$") system tables are not fully supported in the sense that their structure may vary without announcement and that support may not be able to explain all of their contents.
Workaround
As a temporary solution until a fix is provided, you can help the optimizer by pre-materializing small joined tables with strong filters (best scan choices). For this, there is the ORDER BY FALSE optimization that propagates filter downwards before materializing a table.
WITH small_dimension AS (SELECT * FROM small_dimension ORDER BY false) SELECT ... FROM small_dimension JOIN big_fact ON ... WHERE small_dimension ...
Fix
After the version with this fix has been installed, a recomputation of (broken) column statistics should be enforced. To do so please run the following statement as DBA
ANALYZE DATABASE REFRESH STATISTICS;
The statement will recompute column statistics on all tables. There will only be read locks on table level and a COMMIT after each table processed. Hence the database stays operational while being analyzed.
