Database Reference
In-Depth Information
The second time you run the query, you should notice at least a 10% performance speed
increase and should see the following cached in the buffer:
relname | dirty_buffers | num_buffers
---------------+---------------+-------------
facts | 0 | 736
lu_fact_types | 0 | 4
The more onboard memory you have dedicated to the cache, the more room you'll have
to cache data. You can set the amount of dedicated memory by changing the
shared_buf
fers
setting in
postgresql.conf
. Don't go overboard; raising
shared_buffers
too much
will bloat your cache, leading to more time wasted scanning the cache.
Nowadays, there's no shortage of onboard memory. You can take advantage of this by
precaching commonly used tables using an extension called pg_prewarm, now pack‐
aged as
part of PostgreSQL 9.4
. pg_prewarm lets you prime your PostgreSQL by loading
data from commonly used tables into memory so that the first user to hit the database
can experience the same performance boost offered by caching as later users. A good
article that describes this feature is
Prewarming relational data
.
Writing Better Queries
The best and easiest way to improve query performance is to start with well-written
queries. Four out of five queries we encounter are not written as efficiently as they could
be.
There appear to be two primary causes for all this bad querying. First, we see people
reuse SQL patterns without thinking. For example, if they successfully write a query
using a left join, they will continue to use left join when incorporating more tables
instead of considering the sometimes more appropriate inner join. Unlike other pro‐
gramming languages, the SQL language does not lend itself well to blind reuse.
Second, people don't tend to keep up with the latest developments in their dialect of
SQL. If a PostgreSQL user is still writing SQL as if he still had an early version, he would
be oblivious to all the syntax-saving (and sanity-saving) addenda that have come along.
Writing efficient SQL takes practice. There's no such thing as a wrong query as long as
you get the expected result, but there is such a thing as a slow query. In this section, we
point out some of the common mistakes we see people make. Although this topic is
about PostgreSQL, our recommendations are applicable to other relational databases
as well.
Overusing Subqueries in SELECT
A classic newbie mistake is to think of subqueries as independent entities. Unlike con‐
ventional programming languages, SQL doesn't take kindly to the idea of black-boxing
