Greetings,
I'm using the intarray contrib module[1] gin indexes on arrays (obviously) with postgres 9 alpha 4. I am querying to see the existence of an element. When I do the query normally, it performs as I'd expect (very fast). The explain plan looks like what I'd expect, using the index and whatnot. When I put this in SQL function, it performs very slow, as if I didn't have an index on it. (I thought it might be using the default @> operator, so I tried using @@ operator which wasn't a normal array operator, but it still had the issue). I also tried putting the query in a plpgsql to see if that changed things (it didn't).
Then what I did was uninstall the intarray contrib module and created regular gin indexes on the array. When querying by hand it performs fast, but it also seems to use these indexes when in the UDF (so all works when I use the standard gin indexes).
Is there something I am missing in my function declarations or is this possibly a bug?
My Table looks like:
create table followship_rollups
(
max_id bigint not null, -- for sorting
user_id int not null,
append_frozen bool default false not null,
follower_ids int[] not null CHECK (my_array_length(follower_ids) <= 100),
friend_ids int[] not null CHECK (my_array_length(friend_ids) <= 100)
);
create index followship_rollups_expanded_follower on followship_rollups using gin (follower_ids gin__int_ops);
create index followship_rollups_expanded_friend on followship_rollups using gin (friend_ids gin__int_ops);
My function is:
-- Return true or false if the friendship exists
create or replace function has_follower(user_id integer, follower_id integer)
returns boolean
language sql as $$
(select true from followship_rollups where user_id = $1 and follower_ids @> ARRAY[$2])
union all
(select false)
limit 1
;
$$;
Thanks,
Mike Lewis
--
Michael Lewis
lolrus.orgmikelikespie@gmail.com