This is a complex question, and I couldn't form it in a short and easy
way, and I'm sorry for that.
First of all, let me introduce you to the DB (to form a question), for
you to understand what am I talking about. The module looks like a
social network, just the users have friends, which can be in different
groups.
Also it is clustered with PLPROXY by user_id, so the user itself, and
his friends list (the list of ID's) is always in the same DB, but the
information about the friends is not (it is clustered through all the
partitions). Here is a little sketch of a the needed tables:
CREATE TABLE friend
(
id bigint,
user_id integer,
friend_id integer,
group_id bigint,
...
);
This table is a 'friend link' from one user to another, which can be
marked as being in some 'group', and the backward link exists also (from
the 2nd user to the 1st), which can possibly be in another 'group'.
CREATE TABLE user
(
user_id integer,
nickname text,
.... -- lots of other info
);
This is just a user table.
Both of these are clustered by user_id. I need to form the following
query, for it to be as fast as possible (here it is written as if it the
DB was not partitioned):
SELECT something FROM user u, friend f
WHERE u.user_id = f.friend.id
AND f.user_id = $1 (this is given as an argument)
AND f.group_id = $2
So to say, give me the list of friends (not only their ID's, but all the
needed columns!) of given individual, which are in a given group. That
seems ok without plproxy, but with using it, I can't imagine how can I
form a nice query, or a function (or a set of plpgsql + plproxy
functions) to do the job.
Thanks in advance and regards,
Igor Katson.