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

Part I: General PostgreSQL Use

Introduction to PostgreSQL and SQL

Working with Data in PostgreSQL

PostgreSQL SQL Syntax and Use


Part II: Programming with PostgreSQL

Introduction to PostgreSQL Programming

Extending PostgreSQL


The PostgreSQL C APIlibpq

A Simpler C APIlibpgeasy

The New PostgreSQL C++ APIlibpqxx

Embedding SQL Commands in C Programsecpg

Using PostgreSQL from an ODBC Client Application

Using PostgreSQL from a Java Client Application

Using PostgreSQL with Perl

Using PostgreSQL with PHP

Using PostgreSQL with Tcl and Tcl/Tk

Using PostgreSQL with Python

Npgsql: The .NET Data Provider

Other Useful Programming Tools

Part III: PostgreSQL Administration

Introduction to PostgreSQL Administration

PostgreSQL Administration

Internationalization and Localization


Replicating PostgreSQL Data with Slony

Contributed Modules


PostgreSQL(c) The comprehensive guide to building, programming, and administering PostgreSQL databases
PostgreSQL(c) The comprehensive guide to building, programming, and administering PostgreSQL databases
ISBN: 735712573
Year: 2004
Pages: 261 © 2008-2020.
If you may any questions please contact us: