471,075 Members | 1,272 Online

# 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:
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',

and the other on the bit reversed value, B'00001111000011110',

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

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.

TIP 4: Don't 'kill -9' the postmaster

Nov 23 '05 #1
4 2769
Dennis Gearon <ge*****@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

TIP 5: Have you checked our extensive FAQ?

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

Nov 23 '05 #2
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

TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Nov 23 '05 #3
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...es/xindex.html
which describes the behaviors Postgres indexes have.

regards, tom lane

TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Nov 23 '05 #4
Tom Lane wrote:
Since we don't yet have bitmap combining of indexes...

^^^

Are you trying to tell us something ? :-)
Regards
Gaetano Mendola

Nov 23 '05 #5

### This discussion thread is closed

Replies have been disabled for this discussion.