Обсуждение: operators, operator classes, Btree and index usage

Hello All,

I have a following question:

I'm using some set of queries like:

SELECT * FROM
   (SELECT my_function(ra, dec, 0.001) AS ipix1, ra1, dec1
      FROM table1) AS jtable1, table2
        WHERE table2.ipix>=ipix1[1] AND table2.ipix<=ipix1[2]  );

ipix is bigint column, on which the Btree index is created

or dinamically created selects
containing a lot of OR'ed conditions like:

select * from my_table
    WHERE (ipix < 44 AND ipix > 40) OR (ipix <88 AND ipix>66) OR ....

ipix is bigint column,, on which the Btree index is created.

I'm interested in simplifying those queries and introducing the operator
doing something like this:

my_operator(bigint x, bigint[] arr)
checking  the condition:
((x>arr[1]) AND (x<arr[2])) OR ((x>arr[3]) AND (x<arr[4]))

So, the question: Is it possible to create such an operator and to
preserve the Btree index/bitmap scans for previous queries. I understand it
is possible to do with GIST indices (with intarray for example).
But I'm interested whether it is possible with Btree ? Will the  rewriting
of index access methods help to do my task ?
I've read a documentation, but the "Index Access Method Interface" subject
is quite complicated, so currently I don't understand whether it will allow
to solve my problem or not. And as I understand, the simple CREATE OPERATOR,
CREATE OPERATOR CLASS machinery alone is not able to solve my problem, isn't
it ?

Thanks in advance for any advices, replies.

With Best Regards,
        Sergey

*****************************************************
Sergey E. Koposov
Max-Planck Institut fuer Astronomie
Web: http://lnfm1.sai.msu.ru/~math
E-mail: math@sai.msu.ru

"Sergey E. Koposov" <math@sai.msu.ru> writes:
> I'm interested in simplifying those queries and introducing the operator
> doing something like this:

> my_operator(bigint x, bigint[] arr)
> checking  the condition:
> ((x>arr[1]) AND (x<arr[2])) OR ((x>arr[3]) AND (x<arr[4]))

This could be made to work if you define the above as an inline-able SQL
function.  Hacking operator classes won't do it though.

            regards, tom lane

On Fri, 2 Sep 2005, Tom Lane wrote:

> "Sergey E. Koposov" <math@sai.msu.ru> writes:
> > I'm interested in simplifying those queries and introducing the operator
> > doing something like this:
>
> > my_operator(bigint x, bigint[] arr)
> > checking  the condition:
> > ((x>arr[1]) AND (x<arr[2])) OR ((x>arr[3]) AND (x<arr[4]))
>
> This could be made to work if you define the above as an inline-able SQL
> function.  Hacking operator classes won't do it though.


Thank you, Tom! Great! I didn't know that Postgres can inline the SQL
functions (In fact this is because the only place in the documentation
mentioning about inlining SQL functions is the changelog of the
postgres 7.4 :).

With Best Regards,
    Sergey

*****************************************************
Sergey E. Koposov
Max-Planck Institut fuer Astronomie
Web: http://lnfm1.sai.msu.ru/~math
E-mail: math@sai.msu.ru