O'Reilly logo

PostgreSQL 9 Administration Cookbook - Second Edition by Simon Riggs, Gianni Ciolli, Hannu Krosing, Gabriele Bartolini

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. However, you can still try and speed it up through any of the techniques discussed here.

How to do it…

As a first step, you can start providing better information to the optimizer.

If EXPLAIN ANALYZE reveals that the estimates in the database differ a lot from the metrics returned by the actual query execution, you need to instruct PostgreSQL to collect more fine-grained statistics.

The current default statistics target can be shown using this command:

SHOW default_statistics_target;

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

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