correct example of a functional index usage?
От | Dennis Gearon |
---|---|
Тема | correct example of a functional index usage? |
Дата | |
Msg-id | 41785303.8070808@fireserve.net обсуждение исходный текст |
Ответы |
Re: correct example of a functional index usage?
|
Список | pgsql-general |
Is the following example a good, and correct one, for using a functional index? (the 'flip_bits' function would have to be written, and for the correct size of bit(N) ): I think I came up with a better way to search through some dates. It would use a functional index. I am trying to see if any of some appointments fall between two dates. To explain this, think of a 16 day long year, represented by the binary bits in two bytes of a bit string the_table rows: year, date_mask, appointment_title int, bit(16), text 2004, B'11111000011110000'::bit(16), 'appointment_title'::text Day 1 is on the left, day 16 is on the right. Say I wanted to find appointments in the year 2004 that happened between day 2 and day 9. One way that would find them is to search for all records like above that produced a non zero result when the day mask was ANDed against B'0111111110000000' and year = 2004: SELECT appointment FROM the_table WHERE 0 != (date_mask && B'0111111110000000') AND year = 2004; . This compares 9 dates in one operation, VERY fast. Except that it will not use an index - comparing dates alone would, even though there would be 16 times more rows to search, it'd be faster. However, if I make two indexes, one on the standard field value, B'1111000011110000', CREATE INDEX norm_date_mask ON the_table(date_mask); and the other on the bit reversed value, B'00001111000011110', CREATE INDEX flipped_date_mask ON flip_bits(the_table(date_mask)); now I can eliminate all those outside of the date ranges in another, indexed way like so. date_range = B'0111111110000000'; flipped_date_range = flip_bits( B'0111111110000000'); the query looks like this: SELECT appointment FROM the_table WHERE date_mask < date_range AND flipped_date < flip_bits(the_table(date_mask)); Now, I believe indexes are used for BOTH comparisons, and it will be a indexed, fast query, not a total search of the whole table. Plus, with a 365/6 day sized date mask, 365/6 dates are searched in two operations. If I needed SPECIFIC dates only, I could do the above search, and then add another AND condition that did the original AND against a smaller set. I will experiment with this,but I'm pretty sure that I'm right.
В списке pgsql-general по дате отправления: