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

Re: Join with an array

From: Martijn van Oosterhout <kleptog(at)svana(dot)org>
To: Markus Schiltknecht <markus(at)bluegap(dot)ch>
Cc: pgsql-hackers(at)postgresql(dot)org
Subject: Re: Join with an array
Date: 2006-02-23 11:44:47
Message-ID: 20060223114447.GF28530@svana.org (view raw or flat)
Thread:
Lists: pgsql-hackers
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(at)svana(dot)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.

In response to

Responses

pgsql-hackers by date

Next:From: Oleg BartunovDate: 2006-02-23 12:02:50
Subject: Re: Join with an array
Previous:From: Martin PittDate: 2006-02-23 11:42:52
Subject: Re: pg_config, pg_service.conf, postgresql.conf ....

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