Thread: Postgres 9.0 Alpha, GIN indexes, and intarray contrib module, and SQL Functions
Postgres 9.0 Alpha, GIN indexes, and intarray contrib module, and SQL Functions
From
Mike Lewis
Date:
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
Michael Lewis
lolrus.org
mikelikespie@gmail.com
Re: Postgres 9.0 Alpha, GIN indexes, and intarray contrib module, and SQL Functions
From
Tom Lane
Date:
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