03.05.2014 20:48, Leyne, Sean wrote: > > I don't see what statistics will tell the engine. > > Unlike index distribution statistics, which provides some details on the > commonality of values, I don't see how stats on the "orderedness" of a > primary key provide any meaningful details, when the stat is anything other > than 100% ordered
It reports a number of the required "page jumps" (read: page locks/latches and probably cache misses). This may be not an ideal measure, but it's much better than nothing. > The reality is that it is not the orderedness of the index which will matter, > it is how the rows on a data page relate to the rows on the next data page. > > Consider that rows 4,2,3,1 are on page 11 and 7,8,6,5 on page 12. It is a > reasonable argument using the index to read the rows would be valid, since by > reading the page for row 1, rows 2 thru 4 would be in the FB cache, with the > same applying to rows 5 thru 8. But there are plenty of possible cases where > the keys order and the on disk page order would bear no relationship, because > empty pages are reused. > > So, what would be approach if rows 4,2,3,1 are on page 21 and 7,8,6,5 on page > 12. It is a reasonable navigate using the index? How would the stats > determine if the index should be used> I don't see much difference between these two cases, anyway two page fetches are required. If both go to disk, then the latter is likely to be more expensive on HDD (farther heads move), but it could be the other way around as well (pages 11 and 12 may be mapped to very different sectors by the filesystem). I'd say this level of information is out of our control. If you meant to point to a backward/random page navigation, then an example with three data pages would be more interesting. Dmitry ------------------------------------------------------------------------------ "Accelerate Dev Cycles with Automated Cross-Browser Testing - For FREE Instantly run your Selenium tests across 300+ browser/OS combos. Get unparalleled scalability from the best Selenium testing platform available. Simple to use. Nothing to install. Get started now for free." http://p.sf.net/sfu/SauceLabs Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
