Re: Postgres 9.0 Alpha, GIN indexes, and intarray contrib module, and SQL Functions

Mike Lewis <mikelikespie@gmail.com> writes:
> 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).

I wonder whether you are dealing with a search path issue.  Was the
function being created/used with the same search_path as you were
testing by hand?

Some other remarks not directly related to the complaint:

> 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);

Isn't user_id the primary key for this table?  If so, why isn't it
declared that way?  If not, what the heck *is* the intended structure of
this table?  I would think that the performance-critical part of your
query ought to be the "user_id = $1" and the GIN indexes wouldn't be
useful at all (for this particular query anyway).

> 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
>     ;
> $$;

FWIW, this strikes me as pretty non-idiomatic SQL.  I'd have written it
as just... as $$  select exists(select 1 from followship_rollups where user_id = $1 and follower_ids @> ARRAY[$2])$$;

That doesn't seem to explain your performance complaint though.
        regards, tom lane