I have a table with a million of records, i need to select the distinct values of log_type. In other queries i also need to filter log_types result of the distinct and limit the result. But the query is really slow, also this simple one

select distinct log_type
from app_logs;

log_type has an index but nothing change without index, query always use Seq Scan

This is the explain

Aggregate (cost) 119007.80 - 119008.03 (rows) 29 (time) 1923.251
|- Seq Scan (cost) 0.00 - 115796.84 (rows) 1284384  (time) 1300.025

I read other posts but not helped me to find a solution (if exists).

There is any way to improve the performace? Maybe the only my solution is cache the complete distinct results in server application.

ps. i can't define custom functions on db server

  • Get better hardware I guess. Although I must say log_type sound much like an enum, and I would expect select distinct log_type to return more or less the same result each time.
    – mustaccio
    May 20, 2020 at 12:12
  • log_type can be created dynamically, i can't define a enum with prefixed values. I have slow performance also in others db not only on my local instance with poor hardware and i don't think that this can be reduced to an hardware issue. May 20, 2020 at 13:28
  • And how many distinct log_type values do you have? How often do you update the table statistics?
    – mustaccio
    May 20, 2020 at 14:50
  • now log_type have 30 distinct values i don't know the frequence of statistics, i think threre is the default setting of postgresql May 20, 2020 at 15:28


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.