Re: Join with an array

Markus Schiltknecht <markus@bluegap.ch> writes:
> 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);

> I know this is not considered good database design, but it saves a lot
> of overhead for tuple visibility compared to a 1:1 table.

> To fetch an item via the lookup_table I tried to use the following
> query:

> SELECT i.id, i.title FROM item i
>     JOIN lookup_table lut ON i.id = ANY(lut.items)
>     WHERE lut.id = $LOOKUP_ID;

> Unfortunately that one seems to always use a sequential scan over items.

FWIW, "indexcol = ANY(array)" searches are indexable in CVS tip.
There's no hope in any existing release though :-(

> I tried to circumvent the problem with generate_series:

> SELECT i.id, i.title FROM generate_series(0, $MAX) s
>     JOIN lookup_table lut ON s = ANY(lut.items)
>     JOIN item i ON s = i.id
>     WHERE lut.id = $LOOKUP_ID;

Seems like the hard way --- why aren't you searching over array subscripts?

SELECT i.id, i.title FROM generate_series(1, $MAX) sJOIN lookup_table lut ON s <= array_upper(lut.items)JOIN item i ON
i.id= lut.items[s]WHERE lut.id = $LOOKUP_ID;
 

$MAX need only be as large as the widest array in lookup_table.
        regards, tom lane