Postgesql distinct count improve perfomance

Admin 1
Viewed 69

I have a database on Google Cloud SQL. It contains a simple table that looks like this:

url_id user_id

url_id is a string containing an integer number, user_id is a 14 char string. I have an index on url_id:

CREATE INDEX index_test ON table1 (url_id);

The request I want to run is to get the number of distinct user_id that has an url_id that is not in a list of given ids. I do it this way:

 SET work_mem='4GB';
 select count(*) from (select distinct afficheW from table1 where url_id != '1880' and url_id != '2022' and url_id != '1963' and url_id != '11' and url_id != '32893' and url_id != '19' ) t ;

Result:

 count  
---------
 1242298
(1 row)

Time: 2118,917 ms

The table contains 1.8M lines. Is there a way I can make this type of request faster ?

count postgresql sql
4 Answers

Try writing this as:

select count(distinct afficheW)
from table1
where url_id not in (1800, 2022, 1963, 11, 32892, 19);

(This assumes that url_id is really a number, not a string.)

Then add an index on table1(url_id, affichew).

That said, counting over a million items from a table in under two seconds is not so bad.

user_11440350

Unless your WHERE conditions eliminates most of the rows and you can use a partial index, the most promising index would be on (affichew, url_id). That way it can use an index only scan, filter out based on url_id without visiting the table, and get out the rows in the correct order to apply a Unique to them without having to sort or hash.

Also, writing it as not in is slightly faster in my hands than using a list of ANDed != conditions.

user_17212390

An alternative is to use group by instead of distinct:

select
    afficheW
    , count(*)
from
    table1
where
    url_id not in (1800, 2022, 1963, 11, 32893, 19)
group by afficheW;

In this case, most likely you need a separate multicolumn index on afficheW and url_id (as suggested and explained by @jjanes and @GordonLinoff). I think that url_id should be the first column in this multicolumn index, since you have an explicit condition with it in the where clause.

If this query performance is critical, you can use a partial index on afficheW where url_id satisfies your where clause.

As @GordonLinoff, I am also assuming that url_id is numeric (or maybe should be numeric, to save disk space and improve performance), and I am also using not in (...) as a more readable way of writing multiple !=.

SEE ALSO:

Info on column ordering in a multicolumn index (with benchmarks): Multicolumn index and performance

user_9676210

You could try just doing a single level distinct count query here:

select count(distinct afficheW)
from table1
where url_id != '1880' and url_id != '2022' and url_id != '1963' and
      url_id != '11' and url_id != '32893' and url_id != '19';

This at least avoids the outer explicit count query, which doesn't need to be there.

user_18632290
Related