Prev: Proposal: access control jails (and introduction as aspiring GSoC student)
Next: Postgres 9.0 Alpha, GIN indexes, and intarray contrib module, and SQL Functions
From: Mike Lewis on 25 Mar 2010 23:39
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
;
$$;

Full sql is here if you need more info:
http://github.com/mikelikespie/followships/blob/c1a7e8c16159018d7d1154a11169315ac6560178/followships_2.sql
(please
forgive the sloppiness, I haven't cleaned it up yet)

Thanks,
Mike Lewis

[1] http://www.postgresql.org/docs/current/static/intarray.html
--
Michael Lewis
lolrus.org
mikelikespie(a)gmail.com
 | 
Pages: 1
Prev: Proposal: access control jails (and introduction as aspiring GSoC student)
Next: Postgres 9.0 Alpha, GIN indexes, and intarray contrib module, and SQL Functions