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.

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Dennis Gearon <gearond (AT) fireserve (DOT) net> writes:
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

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

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:

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

The site seems to be down Tom.

Tom Lane wrote:
<snip>


---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Tom Lane wrote:


Are you trying to tell us something ? :-)


Regards
Gaetano Mendola