0

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

Improve this question
4
  • 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.
    – Sergio Donati
    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
    – Sergio Donati
    May 20, 2020 at 15:28

0

Reset to default

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.