Re: Join with an array

From: Markus Schiltknecht <markus(at)bluegap(dot)ch>
To: Martijn van Oosterhout <kleptog(at)svana(dot)org>
Cc: pgsql-hackers(at)postgresql(dot)org
Subject: Re: Join with an array
Date: 2006-02-23 12:14:03
Message-ID: 1140696843.12007.8.camel@fotomarburg
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Hello Martijn,

On Thu, 2006-02-23 at 12:44 +0100, Martijn van Oosterhout wrote:
> > SELECT i.id, i.title FROM item i
> > JOIN lookup_table lut ON i.id = ANY(lut.items)
> > WHERE lut.id = $LOOKUP_ID;
>
> At the very least you're going to have to tell us which version you are
> running plus the output of EXPLAIN ANALYZE for that query. Anything
> less and we're guessing. Have you got the appropriate indexes?

Sorry, I knew I'd forget something ;-)

I'm on PostgreSQL 8.1.3. The 'PRIMARY KEY' constraint automatically
creates an index on the lookup_table. The items table as well has an
index on item(id).

Because the other, similar queries use the indices I concluded that in
this first query PostgreSQL _never_ uses an index scan. It also should
not always use it, because the array might be large and a seqscan could
be cheaper in such cases. How should the planer know? In my case,
thought, I assume it would always be cheaper to use an index scan.

If this functionality already exists I was very sorry for the noise and
I beg you to tell me what knobs to fiddle with to make the planner use
the index.

Regards

Markus

In response to

Browse pgsql-hackers by date

  From Date Subject
Next Message Markus Schiltknecht 2006-02-23 12:15:59 Re: Join with an array
Previous Message Oleg Bartunov 2006-02-23 12:02:50 Re: Join with an array