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:

   SELECT listing_id
   FROM   apps
   WHERE  subscription_id = 1298
   ORDER  BY listing_id
   LIMIT  1

   SELECT a.listing_id
   FROM   rcte r
      SELECT a.listing_id
      FROM   apps a
      WHERE  a.subscription_id = 1298
      AND    a.listing_id > 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:

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.