operators, operator classes, Btree and index usage - Mailing list pgsql-general

From Sergey E. Koposov
Subject operators, operator classes, Btree and index usage
Date
Msg-id Pine.LNX.4.44.0509022139380.20863-100000@lnfm1.sai.msu.ru
Whole thread Raw
Responses Re: operators, operator classes, Btree and index usage
List pgsql-general
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


pgsql-general by date:

Previous
From: "Cristian Prieto"
Date:
Subject: Re: Trouble with bytea in SPI...
Next
From: Tom Lane
Date:
Subject: Re: operators, operator classes, Btree and index usage