postgresql COUNT(DISTINCT ...) very slow

performance postgresql count distinct
ferson2020 picture ferson2020 · Jun 28, 2012 · Viewed 178.2k times · Source

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

Ankur picture Ankur · Feb 6, 2013

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)

Related questions

Best way to select random rows PostgreSQL

I want a random selection of rows in PostgreSQL, I tried this: select * from table where random() < 0.01; But some other recommend this: select * from table order by random() limit 1000; I have a very large table with 500 Million rows, I …

Read More
Is it possible to get a history of queries made in postgres

Is it possible to get a history of queries made in postgres? and is it be possible to get the time it took for each query? I'm currently trying to identify slow queries in the application I'm working on. I'm …

Read More
SQL, Postgres OIDs, What are they and why are they useful?

I am looking at some PostgreSQL table creation and I stumbled upon this: CREATE TABLE ( ... ) WITH ( OIDS = FALSE ); I read the documentation provided by postgres and I know the concept of object identifier from OOP but still I do not …

Read More