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
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 |