performance - postgresql COUNT(DISTINCT ...) very slow

Question

I have a very simple SQL query:

SELECT COUNT(DISTINCT x) FROM table;

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;

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.

Answer 1

You can use this:

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

This is much faster than:

COUNT(DISTINCT column_name)