Обсуждение: Indexes intersection

Hi all, (please, look at and analyze the situation)

does BTree index support intersection?
please, I'm not talking about combination of queries, and yes of indexes!

for example: I have 2 indexes, the first in on (date_inclusion ,
date_cancellation ) and the other is in (date_cancellation ,
date_inclusion ).

so, when I do a query like this:
select * from mytable
where (date_inclusion <= '01/01/2003') and (date_cancellation >
'01/01/2003')

I think that PostgreSQL should use those 2 indexes and do an intersection
automatically between they

Roberto de Amorim - +55 48 346-2243
Software engineer at SmartBit Software
roberto@smartbit.inf.br

On Mon, Apr 07, 2003 at 03:25:31PM -0300, Roberto de Amorim wrote:
> for example: I have 2 indexes, the first in on (date_inclusion ,
> date_cancellation ) and the other is in (date_cancellation ,
> date_inclusion ).
>
> so, when I do a query like this:
> select * from mytable
> where (date_inclusion <= '01/01/2003') and (date_cancellation >
> '01/01/2003')
>
> I think that PostgreSQL should use those 2 indexes and do an intersection
> automatically between they

I think you're not asking the question quite right. :) In this case, it
will use one index or the other, because both indexes contain both
fields. I'm guessing what you want to know is if you had two indexes

create index index_a on mytable(date_inclusion)
create index index_a on mytable(date_cancellation)

would the query you show use both indexes, or would it just pick one.

I don't know the answer, but would like to. :)
--
Jim C. Nasby (aka Decibel!)                    jim@nasby.net
Member: Triangle Fraternity, Sports Car Club of America
Give your computer some brain candy! www.distributed.net Team #1828

Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"

On Mon, 7 Apr 2003 15:25:31 -0300, "Roberto de Amorim"
<rbamorim@ig.com.br> wrote:
>I think that PostgreSQL should use those 2 indexes and do an intersection
>automatically between they

No, it doesn't (and I'm not saying it shouldn't).

This has been discussed (cf.
http://archives.postgresql.org/pgsql-hackers/2002-08/msg00481.php),
I don't know whether someone is working on it.

Servus
 Manfred