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
