Search Postgresql Archives

Re: Collapsing multiple subqueries into one

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux