Optimization of PostgreSQL query with filtering by counts

Question

Is there a way to optimize such a query in PostgreSQL 9.5, 9.6?

SELECT x FROM (
    SELECT x,count(x) cnt FROM very_big_table WHERE (conditions) GROUP BY x
) sub
WHERE cnt > 10

Indexes to all WHERE conditions and x is obvios. Any other suggestions?

sagi · Accepted Answer · 2016-10-09 07:26:06Z

2

Try using the HAVING() clause which is used exactly for this purposes (filters on aggregated columns) :

SELECT t.x,count(t.x) as cnt
FROM YourTable t
WHERE < >
GROUP BY t.x
HAVING count(t.x) > 10

answered Oct 9, 2016 at 7:26

sagi

40.6k7 gold badges66 silver badges87 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Thank you for your suggestion, but I do not see any performance change.

Sergey Okatov · Accepted Answer · 2016-10-09 13:35:16Z

0

Well, there is no way to speed up this query but to work on WHERE expression

answered Oct 9, 2016 at 13:35

Sergey Okatov

1,42017 silver badges20 bronze badges