Configuration parameter work_mem in PostgreSQL on Linux

by

I posted your query plan on explain.depesz.com, have a look.

The query planner’s estimates are terribly wrong in some places.
Have you run ANALYZE recently?

Read the chapters in the manual on Statistics Used by the Planner and Planner Cost Constants. Pay special attention to the chapters on random_page_cost and default_statistics_target.
You might try:

ALTER TABLE diplomas ALTER COLUMN number SET STATISTICS 1000;
ANALYZE diplomas;

Or go even a higher for a table with 10M rows. It depends on data distribution and actual queries. Experiment. Default is 100, maximum is 10000.

For a database of that size, only 1 or 5 MB of work_mem are generally not enough. Read the Postgres Wiki page on Tuning Postgres that @aleroot linked to.

As your query needs 430104kB of memory on disk according to EXPLAIN output, you have to set work_mem to something like 500MB or more to allow in-memory sorting. In-memory representation of data needs some more space than on-disk representation. You may be interested in what Tom Lane posted on that matter recently.

Increasing work_mem by just a little, like you tried, won’t help much or can even slow down. Setting it to high globally can even hurt, especially with concurrent access. Multiple sessions might starve one another for resources. Allocating more for one purpose takes away memory from another if the resource is limited. The best setup depends on the complete situation.

To avoid side effects, only set it high enough locally in your session, and temporarily for the query:

SET work_mem = '500MB';

Reset it to your default afterwards:

RESET work_mem;

Or use SET LOCAL to set it just for the current transaction to begin with.

More Related Contents:

  1. Running PostgreSQL in memory only
  2. Postgres query optimization (forcing an index scan)
  3. How does PostgreSQL perform ORDER BY with a b-tree index on the field?
  4. Optimize Postgres query on timestamp range
  5. Multicolumn index on 3 fields with heterogenous data types
  6. In psql, why do some commands have no effect?
  7. Keep PostgreSQL from sometimes choosing a bad query plan
  8. How to log PostgreSQL queries?
  9. How do you create a read-only user in PostgreSQL?
  10. Generating a UUID in Postgres for Insert statement?
  11. Why does PostgreSQL perform sequential scan on indexed column?
  12. PostgreSQL naming conventions
  13. How to set value of composite variable field using dynamic SQL
  14. In-order sequence generation
  15. Does not using NULL in PostgreSQL still use a NULL bitmap in the header?
  16. Postgres password authentication fails
  17. Generate series of dates – using date type as input
  18. Copy a few of the columns of a csv file into a table
  19. Postgres query to check a string is a number
  20. Using pyspark to connect to PostgreSQL
  21. ERROR: permission denied for relation tablename on Postgres while trying a SELECT as a readonly user
  22. How do you create a random string that’s suitable for a session ID in PostgreSQL?
  23. Joining Results from Two Separate Databases
  24. PL/pgSQL functions: How to return a normal table with multiple columns using an execute statement
  25. Update timestamp when row is updated in PostgreSQL
  26. Heroku database restore issue
  27. user postgres launches process that takes all CPUs 100% usage
  28. Postgres table column name restrictions?
  29. How do I get min, median and max from my query in postgresql?
  30. Syntax error at end of input in PostgreSQL

Leave a Comment