On Fri, Apr 8, 2016 at 5:54 AM, Molnár Attila <amol...@mve.hu> wrote:
>
>
> *O*
> *ptimizations* - 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.
- 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.
> - 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.
> - 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.
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
