From: | Chris Hanks <christopher(dot)m(dot)hanks(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Collapsing multiple subqueries into one |
Date: | 2011-08-23 23:54:39 |
Message-ID: | CAK7KUdC=EAHr_h7aXKBKeYwchjCj1L6=OrnUFZ7orVejP-jwPQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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?
From | Date | Subject | |
---|---|---|---|
Next Message | Raghavendra | 2011-08-24 01:46:50 | Re: Wal archiving and streaming replication |
Previous Message | Adrian Klaver | 2011-08-23 23:46:00 | Re: JDBC Connection Errors |