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