pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedmultiple index search with postgres7.1.3 on solaris 8
3+ messages / 2 participants
[nested] [flat]
* multiple index search with postgres7.1.3 on solaris 8
@ 2003-06-12 11:21 Marco Vezzoli <marco.vezzoli@st.com>
0 siblings, 1 reply; 3+ messages in thread
From: Marco Vezzoli @ 2003-06-12 11:21 UTC (permalink / raw)
To: pgsql-sql
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)
Postgres supports queries of the form
SELECT * FROM table WHERE (attribute1,attribute2) IN (subselect)
and
SELECT * FROM table WHERE (attribute1,attribute2) = (value1,value2)
(which strangely is not supported on oracle!).
So I could use the former with a temp table *but* while the latter,
according to EXPLAIN, uses the index the former doesn't. Why?
Thanks in advance for any help,
Marco
--
Marco Vezzoli tel. +39 039 603 6852
STMicroelectronics fax. +39 039 603 5055
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: multiple index search with postgres7.1.3 on solaris 8
@ 2003-06-12 12:07 Bruno Wolff III <bruno@wolff.to>
parent: Marco Vezzoli <marco.vezzoli@st.com>
0 siblings, 1 reply; 3+ messages in thread
From: Bruno Wolff III @ 2003-06-12 12:07 UTC (permalink / raw)
To: Marco Vezzoli <marco.vezzoli@st.com>; +Cc: pgsql-sql
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.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: multiple index search with postgres7.1.3 on solaris 8
@ 2003-06-12 12:29 Marco Vezzoli <marco.vezzoli@st.com>
parent: Bruno Wolff III <bruno@wolff.to>
0 siblings, 0 replies; 3+ messages in thread
From: Marco Vezzoli @ 2003-06-12 12:29 UTC (permalink / raw)
To: bruno@wolff.to; +Cc: 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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2003-06-12 12:29 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-06-12 11:21 multiple index search with postgres7.1.3 on solaris 8 Marco Vezzoli <marco.vezzoli@st.com>
2003-06-12 12:07 ` Bruno Wolff III <bruno@wolff.to>
2003-06-12 12:29 ` Marco Vezzoli <marco.vezzoli@st.com>
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