agora inbox for pgsql-sql@postgresql.org  

help / color / mirror / Atom feed

From: Greg Sabino Mullane <greg@turnstep.com>
To: pgsql-sql@postgresql.org
Subject: Re: Help with 'contestant' query
Date: Fri,  3 Jul 2015 19:36:20 -0000
Message-ID: <c894f75cb31ca5909362d2555f2ce9df@biglumber.com> (raw)
In-Reply-To: <CADDayd0e1suAhwH0__Z3M4VkgBmHkKbb+_rPG75mJJHMb0ZypQ@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-sql>


-----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

Message-ID: <c894f75cb31ca5909362d2555f2ce9df@biglumber.com>
Permalink:  ../c894f75cb31ca5909362d2555f2ce9df@biglumber.com/
Also on:    postgresql.org/message-id/c894f75cb31ca5909362d2555f2ce9df@biglumber.com

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: greg@turnstep.com
  Subject: Re: Help with 'contestant' query
  In-Reply-To: <c894f75cb31ca5909362d2555f2ce9df@biglumber.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox