Message-ID: <3EE87218.79B810F@st.com>
Date: Thu, 12 Jun 2003 14:29:12 +0200
From: Marco Vezzoli <marco.vezzoli@st.com>
MIME-Version: 1.0
To: bruno@wolff.to
Cc: pgsql-sql@postgresql.org
Subject: Re: multiple index search with postgres7.1.3 on solaris 8
References: <3EE8621D.F6A261AE@st.com> <20030612120722.GB1285@wolff.to>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

bruno@wolff.to wrote:
> 
> On Thu, Jun 12, 2003 at 13:21:01 +0200,
>   Marco Vezzoli <marco.vezzoli@st.com> wrote:
> > Hi everybody,
> > I'm sorry if this topic has already been explained, but the search
> > engine at archives.postgresql.org shows me 10 pages of results but
> > without any link (!).
> > I'm using postgres 7.1.3 on Solaris 8; I would like to do some query of
> > this form
> >
> > SELECT * FROM table WHERE (attribute1,attribute2) IN
> > ((value1_0,value2_0),(value1_1,value2_1) ...)
> >
> > (which is legal on oracle 8i on solaris 8).
> > I know this has an equivalent boolean expression but:
> > -I would like to use an index defined like
> >  CREATE INDEX myindex ON table(attribute1,attribute2)
> > -the pairs in the list can be many (up to 100)
> 
> or'ing IN terms together is probably going to result in the same plan that
> you are describing above (assuming table has a large enough number
> of rows). I don't know if this way of writing the query can generate
> a sort of values you are checking against and then a merge join.
> For just a hundred or so values I don't think this plan would be that
> much better than both nest loop and multiple index scans. However, if
> you want to get that plan and or'ing INs won't generate it, then you could
> try union'ing the values together in the IN value list. 7.4 will likely
> behave differently than pre 7.4 versions.

Thanks for the answer, it works (i.e. the planner uses the index). Is
the query limited in length (in characters)?
	Marco

-- 
Marco Vezzoli	   tel. +39 039 603 6852
STMicroelectronics fax. +39 039 603 5055