I am very new to database administration and could use any advice from more expert users of PostgreSQL
My database is an AWS RDS db.m4.large whose specification are here
My queries against one of the larger tables in the database (330 million rows) are running extremely slow and/or timing out, even when I am have a query such as
SELECT(DISTINCT value) FROM mytable;
where there are only 4 or 5 unique values in the
value column. It is not indexed.
pg_size_pretty(pg_total_relation_size(relid)) shows the size of the table to be 118 GB
Even when setting
work_mem to 240 MB, which should allow Postgres to do larger in-memory sorts, the query is equally as slow
EXPLAIN ANALYZE SELECT(DISTINCT value) FROM mytable;
also times out
What does one do to optimize memory usage for querying on a sufficiently large database? Which pg settings would I need to monitor and/or configure differently?
Any help would be appreciated.