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.ml@xxxxxxxxxxx> 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@xxxxxxxxxxxxxx) >>> To make changes to your subscription: >>> http://www.postgresql.org/mailpref/pgsql-general >> >> > > -- > Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
