Postgres 9.0 Alpha, GIN indexes, and intarray contrib module, and SQL Functions
in [PgSql]
|
Prev: [HACKERS] Postgres 9.0 Alpha, GIN indexes, and intarray contrib module, and SQL Functions
Next: [HACKERS] More idle thoughts
From: Tom Lane on 26 Mar 2010 11:05 Mike Lewis <mikelikespie(a)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 -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers |