For tables with more than a hand full of classes, I don't expect the
LATERAL version to come close in performance, since it has to run one lateral subquery per
class. Your version with a subquery (or a similar one with a CTE) will probably be faster.
As for your additional question.
If I remove it [
violation], should the count be done on "discipline"."people_id"?
Yes, that would be the correct alternative, especially for a
LEFT JOIN since it will distinguish between 0 and 1 matches in
discipline. However, since we have an
INNER JOIN and we are also excluding
v < 3 anyway, this distinction is irrelevant here.
count(*) is slightly faster than
count(col), since checking for the existence of a row is enough and the column does not have to be tested for
NULL in addition.
I suggest to use your primary key columns for the query. Simplifies
GROUP BY. Works for PostgreSQL 9.1 or later. Detailed explanation and source in this related answer on SO.
PostgreSQL recognizes functional dependency (allowing columns to be
GROUP BY) only when a table's primary key is included in
GROUP BY list. The SQL standard specifies additional conditions
that should be recognized.
Use proper names for your columns (
id) to make your life simpler.
Demonstrating the CTE alternative:
WITH cte AS (
,row_number() OVER (PARTITION BY p.class ORDER BY COUNT(d.*) DESC) AS v
FROM people p
JOIN discipline d USING (people_id)
GROUP BY p.people_id -- pk is enough
WHERE v < 3;
Probably, the subquery is a bit faster, but test it.
If performance is what you are after, I suggest to aggregate first, then join. That's typically faster, if you query the whole table or most of it.
,row_number() OVER (PARTITION BY class ORDER BY violations DESC) AS rn
FROM people p
SELECT people_id, count(*) AS violations
GROUP BY 1
) d USING (people_id)
WHERE rn < 3
ORDER BY class, rn;
How to break ties?
What if 3 students in one class have 4 strikes against them? Two arbitrary students would be picked this way. Define what to do and adapt your query. You may want to use
rank() instead of
row_number() or add more
ORDER BY items as tiebreaker. Related answer:
PostgreSQL equivalent for TOP n WITH TIES: LIMIT “with ties”?
SQL Fiddle demonstrating both.