Database Reference
In-Depth Information
Details about this setting can be found at
Random Page Cost Revisited
.
The article
suggests the following settings:
• High-end NAS/SAN: 2.5 or 3.0
• Amazon EBS and Heroku: 2.0
• iSCSI and other mediocre SANs: 6.0, but varies widely
• SSDs: 2.0 to 2.5
• NvRAM (or NAND): 1.5
Caching
If you execute a complex query that takes a while to run, subsequent runs are often
much faster. Thank caching. If the same query executes in sequence, by the same user
or different users, and no changes have been made to the underlying data, you should
get back the same result. As long as there's space in memory to cache the data, the planner
can skip replanning or reretrieving. Using common table expressions and immutable
functions in your queries encourages caching.
How do you check what's in the current cache? If you are running PostgreSQL 9.1 or
later, you can install the
pg_buffercache
extension:
CREATE
EXTENSION
pg_buffercache
;
You can then run a query against the
pg_buffercache
view, as shown in
Example 9-11
.
Example 9-11. Are my table rows in buffer cache?
SELECT
C
.
relname
,
COUNT
(
CASE
WHEN
B
.
isdirty
THEN
1
ELSE
NULL
END
)
As
dirty_buffers
,
COUNT
(
*
)
As
num_buffers
FROM
pg_class
AS
C
INNER
JOIN
pg_buffercache
B
ON
C
.
relfilenode
=
B
.
relfilenode
INNER
JOIN
pg_database
D
ON
B
.
reldatabase
=
D
.
oid
AND
D
.
datname
=
current_database
()
WHERE
C
.
relname
IN
(
'facts'
,
'lu_fact_types'
)
GROUP
BY
C
.
relname
;
Example 9-11
returns the number of buffered pages of the
facts
and
lu_fact_types
tables. Of course, to actually see buffered rows, you need to run a query. Try this one:
SELECT
T
.
fact_subcats
[
2
],
COUNT
(
*
)
As
num_fact
FROM
census
.
facts
As
F
INNER
JOIN
census
.
lu_fact_types
AS
T
ON
F
.
fact_type_id
=
T
.
fact_type_id
GROUP
BY
T
.
fact_subcats
[
2
];
