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: ...

Get PostgreSQL 9 Administration Cookbook - Second Edition now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.