PostgreSQL Development Essentials

Bad query performance with stale statistics

Query performance always depends on the plan chosen by the Optimizer. Optimizer again depends on the statistics of corresponding relations to generate a plan for the query. If Postgres has stale statistics, the query might get bad plans irrespective of the existing datasets.

For example, consider we have the following two test tables on which we perform basic operations like the following:

CREATE TABLE out(t INTEGER);
CREATE TABLE inn(t INTEGER);
postgres=# SELECT COUNT(*) FROM out;
count 
-------
1000
(1 row)
postgres=# SELECT COUNT(*) FROM inn;
count 
-------
1000
(1 row)
postgres=# EXPLAIN ANALYZE SELECT * FROM out WHERE t IN(SELECT t  FROM inn);
 QUERY PLAN ---------------------------------------------------------------- ...