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