From: "Greg Sabino Mullane" <greg@turnstep.com>
To: pgsql-sql@postgresql.org
Subject: Re: Help with 'contestant' query
In-Reply-To: 
 <CADDayd0e1suAhwH0__Z3M4VkgBmHkKbb+_rPG75mJJHMb0ZypQ@mail.gmail.com>
Content-type: text/plain; charset=UTF-8
Date: Fri,  3 Jul 2015 19:36:20 -0000
Message-ID: <c894f75cb31ca5909362d2555f2ce9df@biglumber.com>
Precedence: bulk
Sender: pgsql-sql-owner@postgresql.org


-----BEGIN PGP SIGNED MESSAGE-----
Hash: RIPEMD160

John Sherwood asked:

> SELECT  contestants.*, sum(entries.worth) as db_entries, count(entries.id)
> as db_actions FROM "contestants" INNER JOIN "entries" ON
> "entries"."contestant_id" = "contestants"."id" WHERE
> "entries"."campaign_id" IN (SELECT id FROM "campaigns"  WHERE
> "campaigns"."site_id" = $1) AND (entries.status != 'Invalid') GROUP BY
> contestants.id  ORDER BY db_actions desc LIMIT 20 OFFSET 0

If you have a lot of 'Invalid' entries, a partial index will help:

CREATE INDEX index_entries_on_campaign_id_valid
  ON entries(campaign_id) WHERE status <> 'Invalid';

> Here's the explain:

An EXPLAIN ANALYZE is always better, fwiw.

I noticed you have a contestants.* plus a GROUP BY contestants.id, 
which suggests that a) this is not the exact query, or b) id is the only 
column in that table. Either way, if you only need the contestant id, you 
can remove that table from the query, and just use entries.contestant_id instead, 
getting rid of the IN() clause in the process:

SELECT e.contestant_id, SUM(e.worth) AS db_entries, COUNT(e.id) AS db_actions
FROM entries e
JOIN campaigns c ON (c.id = e.campaign_id AND c.site_id = $1)
AND e.status <> 'Invalid'
GROUP BY e.contestant_id
ORDER BY db_actions DESC
LIMIT 20 OFFSET 0;

- -- 
Greg Sabino Mullane greg@turnstep.com
End Point Corporation http://www.endpoint.com/
PGP Key: 0x14964AC8 201507031516
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----

iEYEAREDAAYFAlWW4/UACgkQvJuQZxSWSsgzRgCeLrZAoGZPZV/FSVmSAChFT3lS
FSkAoOEEAbH6/RGMqzNxEaW8Fq6OpA0/
=5/ys
-----END PGP SIGNATURE-----


-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql