O'Reilly logo

PostgreSQL 9 Administration Cookbook by Hannu Krosing, Simon Riggs

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Speeding up queries without rewriting them

Often, you either can't or don't want to rewrite the query. In that case, you often can still speed up the query by the following techniques:

Providing better information to the optimizer

If EXPLAIN ANALYSE reveals that postgreSQL's estimates differ a lot from actual query execution statistics, you need to tell PostgreSQL to collect more fine-grained statistics.

The current default statistics target can be shown by:

show default_statistics_target ;

you can set it to a higher value either in the postgresql.conf file, or if you want to do it only for a single database, you can use ALTER DATABASE as follows:

alter database mydb set default_statistics_target = 200;

Usually, you don't want to set it too high ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required