operators, operator classes, Btree and index usage

From: "Sergey E(dot) Koposov" <math(at)sai(dot)msu(dot)ru>
To: pgsql-general(at)postgresql(dot)org
Subject: operators, operator classes, Btree and index usage
Date: 2005-09-02 18:23:55
Message-ID: Pine.LNX.4.44.0509022139380.20863-100000@lnfm1.sai.msu.ru
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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(at)sai(dot)msu(dot)ru

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Josh Berkus 2005-09-02 19:29:41 Re: PL/pgSQL: EXCEPTION NOSAVEPOINT
Previous Message Cristian Prieto 2005-09-02 18:17:09 Re: Trouble with bytea in SPI...