Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-hackers by date

Next:From: Markus SchiltknechtDate: 2006-02-23 12:15:59
Subject: Re: Join with an array
Previous:From: Oleg BartunovDate: 2006-02-23 12:02:50
Subject: Re: Join with an array

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group