Re: multiple index search with postgres7.1.3 on solaris 8 - Mailing list pgsql-sql

From Marco Vezzoli
Subject Re: multiple index search with postgres7.1.3 on solaris 8
Date
Msg-id 3EE87218.79B810F@st.com
Whole thread Raw
In response to multiple index search with postgres7.1.3 on solaris 8  (Marco Vezzoli <marco.vezzoli@st.com>)
List pgsql-sql
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


pgsql-sql by date:

Previous
From: David Pradier
Date:
Subject: Re: How to make a IN without a table... ?
Next
From: Christoph Haller
Date:
Subject: Re: How to make a IN without a table... ?