Collapsing multiple subqueries into one

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?

Responses

Browse pgsql-general by date

  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