Re: Join with an array

On Thu, Feb 23, 2006 at 12:36:35PM +0100, Markus Schiltknecht wrote:
> Hi,
>
> I'm trying to speed up a query with a lookup table. This lookup table
> gets very big and should still fit into memory. It does not change very
> often. Given these facts I decided to use an array, as follows:
>
> CREATE TABLE lookup_table (id INT PRIMARY KEY, items INT[] NOT NULL);

<snip>

> 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?
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.