Performance Tips

That wraps up the discussion of performance in PostgreSQL. Here are few tips that you should keep in mind whenever you run into an apparent performance problem:

• VACUUM and ANALYZE your database after any large change in data values. This will give the query optimizer a better idea of how your data is distributed.

• Use the CREATE TABLE AS or CLUSTER commands to cluster rows with similar key values. This makes an index traversal much faster.

• If you think you have a performance problem, use the EXPLAIN command to find out how PostgreSQL has decided to execute your query.

• You can influence the optimizer by disabling certain query operators. For example, if you want to ensure that a query is executed as a sequential scan, you can disable the Index Scan operator by executing the following command: " SET ENABLE_INDEX_SCAN TO OFF;" . Disabling an operator does not guarantee that the optimizer won't use that operator ”it just considers the operator to be much more expensive. The PostgreSQL User Manual contains a complete list of runtime parameters.

• You can also influence the optimizer by adjusting the relative costs for certain query operations. See the descriptions for CPU_INDEX_TUPLE_COST , CPU_OPERATOR_COST , CPU_TUPLE_COST , EFFECTIVE_CACHE_SIZE , and RANDOM_PAGE_COST in the PostgreSQL User Manual .

• Minimize network traffic by doing as much work as possible in the server. You will usually get better performance if you can filter data on the server rather than in the client application.

• One source of extra network traffic that might not be so obvious is metadata. If your client application retrieves 10 rows using a single SELECT , one set of metadata is sent to the client. On the other hand, if you create a cursor to retrieve the same set of rows, but execute 10 FETCH commands to grab the data, you'll also get 10 (identical) sets of metadata.

• Use server-side procedures (triggers and functions) to perform common operations. A server-side procedure is parsed, planned, and optimized the first time you use it, not every time you use it.