Keep PostgreSQL from sometimes choosing a bad query plan

by

If the query planner makes bad decisions it’s mostly one of two things:

1. The statistics are inaccurate.

Do you run ANALYZE enough? Also popular in it’s combined form VACUUM ANALYZE. If autovacuum is on (which is the default in modern-day Postgres), ANALYZE is run automatically. But consider:

(Top two answers still apply for Postgres 12.)

If your table is big and data distribution is irregular, raising the default_statistics_target may help. Or rather, just set the statistics target for relevant columns (those in WHERE or JOIN clauses of your queries, basically): 

ALTER TABLE ... ALTER COLUMN ... SET STATISTICS 400;  -- calibrate number

The target can be set in the range 0 to 10000;

Run ANALYZE again after that (on relevant tables).

2. The cost settings for planner estimates are off.

Read the chapter Planner Cost Constants in the manual.

Look at the chapters default_statistics_target and random_page_cost on this generally helpful PostgreSQL Wiki page.

There are many other possible reasons, but these are the most common ones by far.

More Related Contents:

  1. To Do or Not to Do: Store Images in a Database [duplicate]
  2. Copying PostgreSQL database to another server
  3. What are the performance characteristics of sqlite with very large database files? [closed]
  4. Primary keys with Apache Spark
  5. Optimise PostgreSQL for fast testing
  6. When and why are database joins expensive?
  7. How can I put a database under git (version control)?
  8. How do IMMUTABLE, STABLE and VOLATILE keywords effect behaviour of function?
  9. Store query result in a variable using in PL/pgSQL
  10. Can I rollback a transaction I’ve already committed? (data loss)
  11. Kill a postgresql session/connection
  12. PostgreSQL: Drop PostgreSQL database through command line [closed]
  13. What is the optimal length for an email address in a database?
  14. Saving images: files or blobs?
  15. Do stored procedures run in database transaction in Postgres?
  16. Import SQL dump into PostgreSQL database
  17. In what way does denormalization improve database performance?
  18. how to use Blob datatype in Postgres
  19. How can I solve Postgresql SCRAM authentication problem?
  20. Convert SQLITE SQL dump file to POSTGRESQL
  21. Is it better to use multiple databases with one schema each, or one database with multiple schemas? [closed]
  22. How to login and authenticate to Postgresql after a fresh install?
  23. PostgreSQL Database Log Transaction
  24. Connect to a heroku database with pgadmin
  25. How do I automatically update a timestamp in PostgreSQL
  26. Starting and populating a Postgres container in Docker
  27. Adding a new value to an existing ENUM Type
  28. How do you interpret a query’s explain plan?
  29. How can I import a .sql file into my Heroku postgres database?
  30. Backup/Restore a dockerized PostgreSQL database

Leave a Comment