Re: Collapsing multiple subqueries into one

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