-- My default settings (this is basically a single-session machine, so work_mem is pretty high)
SET effective_cache_size='2048MB';
SET work_mem='16MB';

\echo original
EXPLAIN ANALYZE
SELECT
        COUNT (distinct val) as aantal
FROM one
        ;

\echo group by+count(*)
EXPLAIN ANALYZE
SELECT
        distinct val
       -- , COUNT(*)
FROM one
GROUP BY val;

\echo with CTE
EXPLAIN ANALYZE
WITH agg AS (
    SELECT distinct val
    FROM one
    GROUP BY val
    )
SELECT COUNT (*) as aantal
FROM agg
        ;

结果:

original                                                      QUERY PLAN                                                      
----------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=36448.06..36448.07 rows=1 width=4) (actual time=1766.472..1766.472 rows=1 loops=1)
   ->  Seq Scan on one  (cost=0.00..32698.45 rows=1499845 width=4) (actual time=31.371..185.914 rows=1499845 loops=1)
 Total runtime: 1766.642 ms
(3 rows)

group by+count(*)
                                                         QUERY PLAN                                                         
----------------------------------------------------------------------------------------------------------------------------
 HashAggregate  (cost=36464.31..36477.31 rows=1300 width=4) (actual time=412.470..412.598 rows=1300 loops=1)
   ->  HashAggregate  (cost=36448.06..36461.06 rows=1300 width=4) (actual time=412.066..412.203 rows=1300 loops=1)
         ->  Seq Scan on one  (cost=0.00..32698.45 rows=1499845 width=4) (actual time=26.134..166.846 rows=1499845 loops=1)
 Total runtime: 412.686 ms
(4 rows)

with CTE
                                                             QUERY PLAN                                                             
------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=36506.56..36506.57 rows=1 width=0) (actual time=408.239..408.239 rows=1 loops=1)
   CTE agg
     ->  HashAggregate  (cost=36464.31..36477.31 rows=1300 width=4) (actual time=407.704..407.847 rows=1300 loops=1)
           ->  HashAggregate  (cost=36448.06..36461.06 rows=1300 width=4) (actual time=407.320..407.467 rows=1300 loops=1)
                 ->  Seq Scan on one  (cost=0.00..32698.45 rows=1499845 width=4) (actual time=24.321..165.256 rows=1499845 loops=1)
       ->  CTE Scan on agg  (cost=0.00..26.00 rows=1300 width=0) (actual time=407.707..408.154 rows=1300 loops=1)
     Total runtime: 408.300 ms
    (7 rows)

与CTE相同的方案可能也可以通过其他方法产生(窗口函数)

如果你的count(distinct(x))明显比count(x)慢，那么你可以通过在不同的表中维护x值计数来加速这个查询，例如table_name_x_counts (x integer not null, x_count int not null)，使用触发器。但是您的写性能将受到影响，如果您在一个事务中更新多个x值，那么您需要以某种显式的顺序执行此操作，以避免可能的死锁。

你可以用这个:

SELECT COUNT(*) FROM (SELECT DISTINCT column_name FROM table_name) AS temp;

这比:

COUNT(DISTINCT column_name)

我也在搜索相同的答案，因为在某些时候，我需要total_count具有不同的值以及limit/offset。

因为这有点棘手-获得具有不同值的总计数以及限制/偏移。通常情况下，通过限制/抵消很难得到总数。最后我找到了做的方法

SELECT DISTINCT COUNT(*) OVER() as total_count， * FROM table_name limit 2 offset 0;

查询性能也很高。

我也有类似的问题，但是我有很多列要计算。所以我尝试了这两个问题。

计算不同:

SELECT
       to_char(action_date, 'YYYY-MM') as "Month",
       count(*) as "Count",
       count(distinct batch_id)
FROM transactions t
         JOIN batches b on t.batch_id = b.id
GROUP BY to_char(action_date, 'YYYY-MM')
ORDER BY to_char(action_date, 'YYYY-MM');

子查询:

WITH batch_counts AS (
    SELECT to_char(action_date, 'YYYY-MM') as "Month",
           COUNT(*)                        as t_count
    FROM transactions t
             JOIN batches b on t.batch_id = b.id
    GROUP BY b.id
)
SELECT "Month",
       SUM(t_count) as "Transactions",
       COUNT(*)     as "Batches"
FROM batch_counts
GROUP BY "Month"
ORDER BY "Month";

我在大约100k行的测试数据上多次运行这两个查询，子查询方法平均运行约90毫秒，但计数distinct方法平均花费约200毫秒。