postgresql COUNT(DISTINCT...) 매우 느림

postgresql COUNT(DISTINCT...) 매우 느림

매우 간단한 SQL 쿼리가 있습니다.

SELECT COUNT(DISTINCT x) FROM table;

제 테이블에는 약 150만 줄이 있습니다.이 쿼리는 매우 느리게 실행되고 있습니다. 이와 비교하여 약 7.5초가 소요됩니다.

 SELECT COUNT(x) FROM table;

약 435ms가 소요됩니다.성능을 향상시키기 위해 쿼리를 변경할 수 있는 방법이 있습니까?저는 그룹화하고, 정기적으로 카운트하고, x에 인덱스를 달아 보았습니다. 둘 다 실행 시간이 7.5초 같습니다.

다음을 사용할 수 있습니다.

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

이는 다음보다 훨씬 빠릅니다.

COUNT(DISTINCT column_name)

-- 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단일 트랜잭션의 값을 지정하면 교착 상태를 방지하기 위해 명시적인 순서로 이 작업을 수행해야 합니다.

저는 또한 같은 대답을 찾고 있었습니다. 왜냐하면 어느 시점에서 제한/오프셋과 별개의 값을 가진 total_count가 필요했기 때문입니다.

수행하기가 조금 까다롭기 때문에 - 제한/오프셋과 함께 고유한 값을 가진 총 카운트를 얻는 것입니다.일반적으로 제한/오프셋으로 총 카운트를 얻는 것은 어렵습니다.마침내 나는 할 수 있는 방법을 얻었습니다.

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 행의 테스트 데이터에서 이 두 쿼리를 모두 여러 번 실행했는데, 하위 쿼리 접근 방식은 평균 ~90ms로 실행되었지만 카운트 구별 접근 방식은 평균 약 200ms가 소요되었습니다.

언급URL : https://stackoverflow.com/questions/11250253/postgresql-countdistinct-very-slow