Re: Collapsing multiple subqueries into one

On 24/08/2011, at 4:44 PM, Chris Hanks wrote:

> 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...?

I'm not sure, Chris - perhaps others on the mailing list can answer this?

>
>
>
> 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
>>
>>
>
> --
> 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