where in (select array)

[Marcus Engene <mengpg2@xxxxxxxxx>]

Hi List,

I have the might_like table that contains products a user might like if 
he likes the present one (item).

CREATE TABLE might_like
(
item                       INTEGER NOT NULL
,created_at                 TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL
,child                      INTEGER NOT NULL
)
WITHOUT OIDS;

CREATE INDEX might_like_x1 ON might_like(item);

Since there are (will be) houndreds of thousands of items, and 20+ might 
like items, i thought it would be nice to reduce the set to 1/20th by 
using a vector.

CREATE TABLE might_like_vector
(
item                       INTEGER NOT NULL
,created_at                 TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL
,child_arr                  INTEGER[]
)
WITHOUT OIDS;

CREATE INDEX might_like_vector_x1 ON might_like_vector(item);

But then this don't work:

select
   ...
from
   item pic
where
   pic.objectid in (
       select mlv.child_arr
       from might_like_vector mlv
       where mlv.item = 125 AND
             mlv.created_at > now() - interval '1 week'
   )
limit 16

This seems to work but is ugly:

select
   ...
from
   item pic
where
   pic.objectid in (
       select mlv.child_arr[s.a]
       from might_like_vector mlv
           ,generate_series(1,20) as s(a)
       where mlv.item = 125 AND
             mlv.created_at > now() - interval '1 week'
   )
limit 16

Is there a better way to do it?

Thanks,
Marcus


--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general