Re: Collapsing multiple subqueries into one

Thanks Royce. I put together another query using a WITH statement
that's also working:

WITH v AS (
SELECT item_id, type, direction, array_agg(user_id) as user_ids
FROM votes
WHERE root_id = 5305
GROUP BY type, direction, item_id
ORDER BY type, direction, item_id
)
SELECT *,
(SELECT user_ids from v where item_id = i.id AND type = 0 AND
direction = 1) as upvoters,
(SELECT user_ids from v where item_id = i.id AND type = 0 AND
direction = -1) as downvoters,
(SELECT user_ids from v where item_id = i.id AND type = 1) as favoriters
FROM items i
WHERE root_id = 5305
ORDER BY id

It feels more sensible to me, but it's slightly slower than my initial
attempt (15 ms vs. 13 ms, when running as a prepared statement to
avoid any query parsing overhead, and averaging the time over several
thousand queries). I'm not sure why...?

On Tue, Aug 23, 2011 at 8:14 PM, Royce Ausburn <royce(dot)ml(at)inomial(dot)com> wrote:
> This might help you:
>
> http://www.postgresql.org/docs/8.4/static/queries-with.html
>
> On 24/08/2011, at 9:54 AM, Chris Hanks wrote:
>
>> I have two tables:
>>
>> CREATE TABLE items
>> (
>>  root_id integer NOT NULL,
>>  id serial NOT NULL,
>>  -- Other fields...
>>
>>  CONSTRAINT items_pkey PRIMARY KEY (root_id, id)
>> )
>>
>> CREATE TABLE votes
>> (
>>  root_id integer NOT NULL,
>>  item_id integer NOT NULL,
>>  user_id integer NOT NULL,
>>  type smallint NOT NULL,
>>  direction smallint,
>>
>>  CONSTRAINT votes_pkey PRIMARY KEY (root_id, item_id, user_id, type),
>>  CONSTRAINT votes_root_id_fkey FOREIGN KEY (root_id, item_id)
>>      REFERENCES items (root_id, id) MATCH SIMPLE
>>      ON UPDATE CASCADE ON DELETE CASCADE,
>>  -- Other constraints...
>> )
>>
>> I'm trying to, in a single query, pull out all items of a particular
>> root_id along with a few arrays of user_ids of the users who voted in
>> particular ways. The following query does what I need:
>>
>> SELECT *,
>>  ARRAY(SELECT user_id from votes where root_id = i.root_id AND
>> item_id = i.id AND type = 0 AND direction = 1) as upvoters,
>>  ARRAY(SELECT user_id from votes where root_id = i.root_id AND
>> item_id = i.id AND type = 0 AND direction = -1) as downvoters,
>>  ARRAY(SELECT user_id from votes where root_id = i.root_id AND
>> item_id = i.id AND type = 1) as favoriters
>> FROM items i
>> WHERE root_id = 1
>> ORDER BY id
>>
>> The problem is that I'm using three subqueries to get the information
>> I need when it seems like I should be able to do the same in one. I
>> thought that Postgres (I'm using 8.4) might be smart enough to
>> collapse them all into a single query for me, but looking at the
>> explain output in pgAdmin it looks like that's not happening - it's
>> running multiple primary key lookups on the votes table instead. I
>> feel like I could rework this query to be more efficient, but I'm not
>> sure how.
>>
>> Any pointers?
>>
>> --
>> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-general
>
>