Analysing SQL Queries
Make use of EXPLAIN clause in your database engine to run a query analyse:
EXPLAIN
SELECT
user_id,
COUNT(DISTINCT (user_id))
FROM
users.post
GROUP BY
user_id
HAVING
COUNT(DISTINCT (user_id)) > 1;
It will show you how the query is being executed:
| QUERY PLAN |
| ----------------------------------------------------------------------------- |
| GroupAggregate (cost=790088.08..848298.55 rows=929342 width=12) |
| Group Key: user_id |
| Filter: (count(DISTINCT user_id) > 1) |
| -> Sort (cost=790088.08..797874.79 rows=3114687 width=4) |
| Sort Key: user_id |
| -> Seq Scan on post (cost=0.00..368989.87 rows=3114687 width=4) |
| JIT: |
| Functions: 8 |
| Options: Inlining true, Optimization true, Expressions true, Deforming true |
This shows you how the query is being executed, leaving you a feedback for optimization.