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
