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.

  • Unrelated, but: putting the select list between parentheses is completely useless. select distinct column from... is the same thing as select (distinct column) from. Mar 17 '19 at 7:46
  • Do you have an index on that column? Mar 17 '19 at 7:47
  • "EXPLAIN ANALYZE SELECT(DISTINCT value) FROM mytable; also times out" Change the time out.
    – jjanes
    Mar 17 '19 at 13:28
  • It should probably be using a hash aggregate rather than a sort. What does EXPLAIN without ANALYZE show?
    – jjanes
    Mar 17 '19 at 13:33
  • 1
    FWIW, SELECT (DISTINCT value) FROM mytable; gives an error in PostgreSQL versions 8.4 - 11. This syntax works SELECT DISTINCT(value) FROM mytable; (and is, IMHO, by far the most legible) - see fiddle here. The EXPLAIN ANALYZE in the fiddle is worthless, because there are too few records - the INDEX will be ignored. I'd try indexing the field!
    – Vérace
    Mar 17 '19 at 15:57

Your Answer

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

Browse other questions tagged or ask your own question.