Обсуждение: correct example of a functional index usage?
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.
Dennis Gearon <gearond@fireserve.net> writes: > I am trying to see if any of some > appointments fall between two dates. > 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, No, an index can be used for one or the other. Since we don't yet have bitmap combining of indexes, you don't get to apply two indexes in a single query. Even if you did, this would be relatively inefficient since each index would return a whole lotta rows. Why don't you just do the straightforward thing and look for WHERE appointment_date >= 'some_date' AND appointment_date <= 'some-other-date' AFAICS that solves the stated problem. Maybe you were not being clear about what you want? regards, tom lane
But it is possible to use multiple indexes on dates, and that is why the one at the bottom works, right? Would a single index get used for SELECT appointment FROM the_table WHERE 0 <> (date_mask && date_range); Tom Lane wrote: > > > No, an index can be used for one or the other. Since we don't yet have > bitmap combining of indexes, you don't get to apply two indexes in a > single query. Even if you did, this would be relatively inefficient > since each index would return a whole lotta rows. > > Why don't you just do the straightforward thing and look for > > WHERE appointment_date >= 'some_date' > AND appointment_date <= 'some-other-date' > > AFAICS that solves the stated problem. Maybe you were not being clear > about what you want? > > regards, tom lane >
Dennis Gearon <gearond@fireserve.net> writes: > But it is possible to use multiple indexes on dates, and that is why the one at the bottom works, right? No, it is possible to use multiple conditions that are relevant to a single index. A range query like "where x >= lobound and x <= hibound" works very nicely with a btree index on x. But "where x >= lobound and y <= hibound" isn't a range query. > Would a single index get used for > SELECT appointment > FROM the_table > WHERE 0 <> (date_mask && date_range); I don't see any indexable operator there at all. You might care to read http://developer.postgresql.org/docs/postgres/xindex.html which describes the behaviors Postgres indexes have. regards, tom lane
The site seems to be down Tom. Tom Lane wrote: <snip> >I don't see any indexable operator there at all. You might care to read >http://developer.postgresql.org/docs/postgres/xindex.html >which describes the behaviors Postgres indexes have. > > regards, tom lane > > >
Tom Lane wrote: > Since we don't yet have bitmap combining of indexes... ^^^ Are you trying to tell us something ? :-) Regards Gaetano Mendola