3

In PostgreSQL 9.4 I have a table:

id         p_id
PK        integer
-----------------
1           1
2           1
.............
123122       2233

Is there a way to count all distinct p_id values in the table with only one query (without using subqueries). I'm free to use any window-function.

Mureinik
  • 297,002
  • 52
  • 306
  • 350
user3663882
  • 6,957
  • 10
  • 51
  • 92

1 Answers1

4

You can use the distinct modifier in the count function:

SELECT COUNT(DISTINCT p_id) FROM mytable
Mureinik
  • 297,002
  • 52
  • 306
  • 350
  • Interesting, why does somenone downvote the post? Is it a bad way to do that? – user3663882 Jun 20 '15 at 13:04
  • Postgres and Hive are two databases where `count()` with a subquery using `distinct` is often faster than `count(distinct)`. I sincerely doubt that the anonymous downvoter was thinking about this detail of the execution plan. This is the correct answer. And, the cases where `count(distinct)` produces a suboptimal execution plan are an opportunity for Postgres developers to further improve the database in the future. – Gordon Linoff Jun 20 '15 at 13:07
  • @GordonLinoff I admit I did not know that about Postgres, thanks! Learnt something new today! I'll have to play around with my local database a bit to see how I can reproduce this. – Mureinik Jun 22 '15 at 05:53
  • @Mureinik . . . You can see the behavior on multi-processor servers. The key idea is that the "aggregation" part of a `group by` runs in parallel, but the "function" part of evaluating an aggregation function is serialized. This can have a big impact on performance. – Gordon Linoff Jun 22 '15 at 12:17