Re: [Firebird-devel] Feature request & discussion for V4 (same as for V3)

[Molnár Attila]

On 2016.04.09. 20:25, Ann Harrison wrote: On Fri, Apr 8, 2016 at 5:54 AM, Molnár Attila <amol...@mve.hu> wrote: Optimizations     - IS NOT NULL should use index. It is equivalent with >= min_value or <= max_value based on index direction  Histograms and clustered indexes (if they're being considered) could help here to detect cases where IS NOT NULL returns a small subset of the records in a table.  In general, searches that touch more than half the records in a table are more efficient when made in storage (natural) order rather than through an index.  Remember that Firebird stored data and indexes separately, so setting up an indexed retrieval that will touch every page in a table is just overhead compared with straight-forwardly reading every page. I ment min_value and max_value not base on table data, but on column data type.     - condition pre-evaluation and reduction. e.g.: WHERE 1 = 2 AND field = :param is always FALSE. Evaluation does not needed for all records, can decide at prepare time whether the result is an empty result set or an unfiltered result set. When InterBase was created, there was a lot of academic work on optimizing corner cases, with the result that academic databases tended to spend more time optimizing than retrieving.  We made the deliberate choice not to spend optimizer time saving idiots from themselves.  Thirty years later, maybe we'd choose differently.  However, lots of programs depend on tricks like +0 and concatenating with an empty string to coerce unnatural but effective plans.  I'd worry about the damage done to those cases. This optimization can only be done if constant conditions are in the SQL, so +0 like tricks would not be affected.       - use index in "NATURAL" mode when column in a conditional appears in a multi column index, but not in the first place. You may reduce number of database page visits in this way : index page can hold more effective record data because it's narrower than the table data page record (also in worst case it could be worse than NATURAL because ot the mixed index and table data page read, but I think overall it could worth it, especially in big tables. measurements needed) I not sure what you mean by "NATURAL" index mode - "natural" usually means reading the data pages in storage order without any index. If you mean reading across the leaf level of the index to find matches in the second and subsequent keys in an index, you have no idea how hard that would be.  Firebird index keys are mashed up values created so they compare bytewise in the desired order.  When using an index, Firebird hasn't a clue where the boundaries fall between columns in multi-column index.  It's just bytes.   The format makes indexes dense and comparisons quick.   Changing the key format to support partial matches on second and third columns seems like a bad idea, given that there's very little difference between having an index on each column and a multi-column index.  Remember that Firebird uses multiple indexes on a single table. I ment the second case (reading across the leaf level). It's hard and costly (or even possible) to deconstruct the index format to get the original column values? The question is reading all leaf deconstruct and filter is faster than reading all records natural an filter.       - SELECT DISTINCT <indexed fields> FROM table is slow (natural scan on all records) and SELECT <indexed fields> FROM table GROUP BY <indexed fields> is also slow (worse! : index scan on all records). I think in this case it's not necessary to read all the records in the table, it should be enough to read #of distinct <indexed fields> values from table. (currently you have to keep a separate table with this information because you can't access to this information fast) Unh, no.  Indexes are multi-generational structures, so they often contain more entries than there are records visible to any one transaction.  At a minimum, you've got to touch the records that appear good candidates from the index. OK, but why touch ALL records in the table? In this case after first confirmation you could skip all the other records on this index value and continue on next one. Good luck, Ann ------------------------------------------------------------------------------ Find and fix application performance issues faster with Applications Manager Applications Manager provides deep performance insights into multiple tiers of your business applications. It resolves application problems quickly and reduces your MTTR. Get your free trial! http://pubads.g.doubleclick.net/ gampad/clk?id=1444514301&iu=/ca-pub-7940484522588532 Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel

------------------------------------------------------------------------------
Find and fix application performance issues faster with Applications Manager
Applications Manager provides deep performance insights into multiple tiers of
your business applications. It resolves application problems quickly and
reduces your MTTR. Get your free trial! http://pubads.g.doubleclick.net/
gampad/clk?id=1444514301&iu=/ca-pub-7940484522588532
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel