| Subject | Re: [firebird-support] Multi-segment indices |
|---|---|
| Author | Ann Harrison |
| Post date | 2012-08-30T14:30:48Z |
On Thu, Aug 30, 2012 at 5:34 AM, jon_neve <jn@...> wrote:
requires reading and sorting the table once, while creating two single
segment indexes requires reading and sorting the table twice. But you're
really not interested in the cost of creating an index, are you?
Firebird reads one index, setting bits in a bitmap for each record number
from the index that has a record that satisfies that condition, then reads
then next, also setting bits. When it runs out of indexes, it combines the
bitmaps and uses the result to determine which records to read. So, if the
combination of producer and date is unique, combining the bitmaps from the
producer index and the date index will result in a bitmap with one bit set
- or none if there's no such record.
If you always (or very often) use both producer and date and know that you
never have two records from the same producer on the same date, then a
combined unique index has the advantage that Firebird's optimizer will know
that it only needs to look at that index to produce at most one record.
Generally, I recommend separate indexes. They're useful in more cases.
Good luck,
Ann
(The exception to the bitmap index lookup is a query that includes FIRST or
LIMIT where the ORDER BY terms are indexed. In that case, Firebird will
walk the index to get the rows in order)
[Non-text portions of this message have been removed]
>The straight forward answer is that creating one multi-segment index
> I am wondering, is there a difference in performance between creating a
> multi-segment index (on two fields for example), and creating two separate
> indices (for the same two fields)?
requires reading and sorting the table once, while creating two single
segment indexes requires reading and sorting the table twice. But you're
really not interested in the cost of creating an index, are you?
> What I'm wondering is whether or not an index on (date, producer) would beNormally when a query has conditions on fields in more than one index,
> better in this situation than two separate indices, one for each field. I
> have a situation where the combination of the two fields should actually be
> unique, and so it should be a very good index.Would Firebird be able to
> combine the two separate indices in such a way as to be able to use them as
> efficiently as one double-segment index? The obvious drawback of the
> double-segment index being that it's less versatile, as it can only be used
> in queries where both fields are used as search criteria...
>
>
Firebird reads one index, setting bits in a bitmap for each record number
from the index that has a record that satisfies that condition, then reads
then next, also setting bits. When it runs out of indexes, it combines the
bitmaps and uses the result to determine which records to read. So, if the
combination of producer and date is unique, combining the bitmaps from the
producer index and the date index will result in a bitmap with one bit set
- or none if there's no such record.
If you always (or very often) use both producer and date and know that you
never have two records from the same producer on the same date, then a
combined unique index has the advantage that Firebird's optimizer will know
that it only needs to look at that index to produce at most one record.
Generally, I recommend separate indexes. They're useful in more cases.
Good luck,
Ann
(The exception to the bitmap index lookup is a query that includes FIRST or
LIMIT where the ORDER BY terms are indexed. In that case, Firebird will
walk the index to get the rows in order)
[Non-text portions of this message have been removed]