Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
Performance Tuning
Although it is common to blame hardware for database sluggishness, most often, improper database design causes the sluggishness. Fortunately, you can tune the underlying database code to improve performance. When doing so, you should follow some general rules:
Have indexes on commonly queried fields. Any fields in which joins are being done, or any fields that are the focus of numerous SELECT...WHERE clauses, should be indexed. Indexes do help with selection, but they penalize insertions or updates. Therefore, having every field indexed is not a good idea.
Use explicit transaction. If numerous tables are being updated or inserted in a series, encapsulating the statements inside one BEGIN...COMMIT clause can significantly improve performance.
Use cursors. Using cursors can dramatically improve system performance. In particular, using cursors to generate lists for user selection can be more efficient than running numerous isolated queries.
Limit use of triggers and rules. Although triggers and rules are an important part of data integrity, overuse severely impacts system performance.
Use explicit JOIN commands. Starting with PostgreSQL version 7.1, it is possible to control how the query planner operates by using an explicit JOIN syntax. For instance, the following queries produce the same results, but the second unambiguously provides the query planner with the order in which to proceed:
SELECT * FROM x,y,z WHERE x.name=y.name AND y.age=z.age; SELECT * FROM x JOIN (y JOIN z ON (y.age=z.age)) ON (x.name=y.name);
Enforce logic mechanisms on the front-end. Enforcing some minimal standards on the front end of a database application can improve overall system performance. Checking input fields for valid or minimal information requirements can eliminate the need to do expensive queries.