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 ---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend
