Markus Schiltknecht <[EMAIL PROTECTED]> 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) s
JOIN 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
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
