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

Mike Lewis <mikelikespie(at)gmail(dot)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