From: Marco Vezzoli <marco.vezzoli@st.com>
To: bruno@wolff.to
Cc: pgsql-sql@postgresql.org
Subject: Re: multiple index search with postgres7.1.3 on solaris 8
Date: Thu, 12 Jun 2003 14:29:12 +0200
Message-ID: <3EE87218.79B810F@st.com> (raw)
References: <3EE8621D.F6A261AE@st.com>
<20030612120722.GB1285@wolff.to>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: marco.vezzoli@st.com, bruno@wolff.to
Subject: Re: multiple index search with postgres7.1.3 on solaris 8
In-Reply-To: <3EE87218.79B810F@st.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox