Thread: 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