postgresql COUNT（DISTINCT ...）非常慢 [英] postgresql COUNT(DISTINCT ...) very slow

问题描述

我有一个非常简单的SQL查询：

I have a very simple SQL query:

SELECT COUNT(DISTINCT x) FROM table;

我的表有大约150万行。这个查询运行得很慢;它需要大约7.5s，相比

My table has about 1.5 million rows. This query is running pretty slowly; it takes about 7.5s, compared to

 SELECT COUNT(x) FROM table;

大约需要435ms。有什么方法可以改变我的查询来提高性能吗？我试过分组和做一个常规计数，以及在x上放一个索引;

which takes about 435ms. Is there any way to change my query to improve performance? I've tried grouping and doing a regular count, as well as putting an index on x; both have the same 7.5s execution time.

推荐答案

您可以使用：

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

这比：

COUNT(DISTINCT column_name)

这篇关于postgresql COUNT（DISTINCT ...）非常慢的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！