EmbeddingGemma 2: an open, lightweight multimodal embedding model
deepmind.google·6d ago
AI Engineering
Hacker News·Aug 6·5 min read
The DISTINCT in Your COUNT
TL;DR
Counting distinct users in Postgres can be misleadingly expensive due to the DISTINCT keyword, which disables parallel query execution. This can significantly impact performance on large datasets.
✦ Why It Matters
Avoid using DISTINCT in large queries to maintain query performance in Postgres.
Key Takeaways
- Avoid COUNT(DISTINCT) for large datasets to prevent serial execution.
- Use GROUP BY to enable parallel processing in PostgreSQL.
- Monitor query performance with EXPLAIN to identify inefficiencies.
- Refactor queries to improve execution time significantly.
How It Works
PostgreSQL's parallel aggregation works by allowing multiple workers to process data simultaneously. When using COUNT, each worker can maintain a partial count, which the leader process combines at the end.
However, COUNT(DISTINCT) requires a global view of unique values, preventing this parallelization. By using GROUP BY, each worker can independently aggregate data, allowing for efficient parallel processing.