Distinct counts from large table are too slow I have a table with 1.2 billion rows. The table has 60-70 columns, the vast majority of which are empty.

The table is indexed on subscription_id, among other indexes.

My query is:

SELECT count(distinct listing_id)
FROM apps
WHERE subscription_id = 1298;


This particular subscription_id is going to have roughly 30M rows to count. Unfortunately I am unable to do so as the query takes in excess of one hour just to  get that count.

I know there must be some way to improve it. Would it be improved if I created an index on subscription_id and listing_id together?
Would it be improved if I created an index on subscription_id and listing_id together?


Possibly yes, an index on (subscription_id, listing_id) (columns in this order!) might very well allow a cheaper index-only scan. You did not provide enough information to say more.

If it's all about this particular subscription_id, then a partial index would help a lot more:

CREATE INDEX ON apps (listing_id) WHERE subscription_id = 1298;


If there are more than a few duplicate listing_id per subscription_id, use 
this adapted query with a recursive CTE:

WITH RECURSIVE rcte AS (
   (
   SELECT listing_id
   FROM   apps
   WHERE  subscription_id = 1298
   ORDER  BY listing_id
   LIMIT  1
   )

   UNION ALL
   SELECT a.listing_id
   FROM   rcte r
   JOIN   LATERAL (
      SELECT a.listing_id
      FROM   apps a
      WHERE  a.subscription_id = 1298
      AND    a.listing_id &gt; r.listing_id
      ORDER  BY a.listing_id
      LIMIT  1
      ) a ON a.listing_id IS NOT NULL
   )
SELECT count(*) FROM rcte;


Requires index from above to be fast. For many duplicates it can be faster by orders of magnitude. The point is to work around a still missing (up to Postgres 12) index skip scan. See:


Fast way to count distinct column values (using an index?)
Optimize GROUP BY query to retrieve latest row per user


If, OTOH, there are no duplicate listing_id, then get rid of DISTINCT to make it a lot faster.