Performance & Concurrency - PostgreSQL 9 Administration - page 251

Use materialized views (long-living temp tables)
If the part you put in the temporary table is large, does not change very often, and/
or is hard to compute, then you may be able to do it less often for each query by using
a technique named materialized views. Materialized views are views that are prepared
before they are used and are either fully regenerated as underlying data changes or in
some cases can update only those rows that depend on the changed data. As of version
9.0, there is no explicit support for materialized views in PostgreSQL; that is, you can't just
"CREATE MATERIALIZED VIEW AS ...", but there are several sample implementations for
achieving exactly the same functionality. Visit http://wiki.postgresql.org/wiki/
Materialized_Views for more discussion and examples.
Using set-returning functions for some parts of queries
Another possibility to achieve functionality similar to temp tables and/or materialized views is
using a set-returning function for some part of the query.
For example, it is easy to have a materialized view freshness check inside a function.
There are also samples of materialized views and set-returning function usage in shop_
database.tar.gz .
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 for all tables and fields, as it slows down the ANALYSE
command, so PostgreSQL gives you a more fine-grained way of doing it on a field-by-field basis.
alter table mytable alter col_with_bad_stats set statistics 500;
The new statistics values take effect at the next time ANALYSE is run on the table, so it makes
sense to run ANALYSE after changing these values.