Re: Optimizer Issues with 10.00.xC8

More details: My scenario seems to have to do with the amount and type of data I have in the table. There are about 186.5 million rows in the table: SELECT COUNT(*) FROM sftst; (count(*)) 186523102 1 row(s) retrieved. After populating it, I run UPDATE STATISTICS LOW FOR TABLE sftst; and then I run this query: SELECT idxname[1,16] AS idx, ROUND(nrows/1000000,1)::CHAR(6) AS mrows, levels::CHAR(2) AS lv, leaves::CHAR(10) AS leaves, nunique::CHAR(10) AS nunique, clust::CHAR(10) AS clust, colmin::CHAR(8) AS colmin, colmax::CHAR(10) AS colmax FROM sysindexes AS i, systables AS t, syscolumns AS c WHERE i.tabid = t.tabid AND i.tabid = c.tabid AND i.part1 = c.colno AND t.tabname = "sftst"; colname colmin colmax col1 23432150 418212168 col2 20104 1506395442 col3 col4 col5 Next, UPDATE STATISTICS MEDIUM FOR TABLE sftst(col3, col4, col5) DISTRIBUTIONS ONLY; and then repeat the above query: colname colmin colmax col1 23432150 418212168 col2 20104 1506395442 col3 col4 col5 Then UPDATE STATISTICS HIGH FOR TABLE sftst(col2); and run again: colname colmin colmax col1 23432150 418212168 col2 20104 1506395442 col3 col4 col5 Then UPDATE STATISTICS HIGH FOR TABLE sftst(col1); and run again: colname colmin colmax col1 23432150 418212168 col2 20104 1506395442 col3 col4 col5 Then UPDATE STATISTICS LOW FOR TABLE sftst(col1,col3); and run again: colname colmin colmax col1 col2 20104 1506395442 col3 col4 col5 So it seems that it's this last statement that does the trick. What might be relevant here is that the index on (col1,col3) is fragmented three ways, with a MOD expression on col1.