02.05.2014 22:03, Leyne, Sean wrote: > >> It depends. Primary key may be stored more-or-less in regard to physical disk >> location. And the measure how good is INDEX vs NATURAL scan can be >> available to the optimizer (index clustering factor). > > Without the knowledge of way that Primary Keys map to storage order an > assumption based on same would be just as bad a assuming that UDF are by > default deterministic, no?
I'm not talking about assumptions, the statistics either proves it or not. > Further, wouldn't an Index clustering factor really be of little value as the > number of exceptions to the physical vs. primary key order increase? Since > any exception to key order would result in random IO, which can quickly kill > system performance vs. natural scan. We don't compare INDEX vs NATURAL, we compare INDEX vs SORT(NATURAL). There should be plenty random page jumps to exceed the external sort cost. >>> With an MVCC it is possible for all rows to have both a NULL and NOT >>> NULL values stored in the field index, so reading each rows is >>> required. But as noted in #1 above, reading by index can lead to >>> significant disk IO/degradation >> >> Who cares if the statistics tells us that NULLs are 95% of all keys? > > Please clarify your context for "Who cares", I don't quite follow your point. There's virtually no difference between scanning the index for IS NULL and for IS NOT NULL. MGA effects and costs are absolutely the same. The only thing that matters is the predicate selectivity. It's surely not a guarantee anyway -- all versions with non-NULL key may be invisible to the current transaction thus killing the performance. But once again, it's absolutely the same as for IS NULL or any other condition. I fail to see why you insist on the problem for IS NOT NULL only. 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
