Database Reference
In-Depth Information
First, watch the CPU idle reading in the top. If this is in low single-digits for most of the time,
you probably have problems with CPU power.
If you have a high load average with still lots of CPU idle left, you are probably out of disk
bandwidth. In this case, you should also have lots of postgres processes in status
D
.
Reducing the number of rows returned
Although often the problem is producing many rows in the first place, it is made worse by
returning all the unneeded rows to the client. This is especially true if client and server
are not on the same host.
Here are some ways to reduce the traffic between the client and server.
A full text search returns 10,000 documents, but only first the 20
are displayed to user
In this case, order the documents by ranking on the server, and return only the top 20
actually displayed
SELECT title, ts_rank_cd(body_tsv, query, 20) AS text_rank
FROM articles, plainto_tsquery('spicy potatoes') AS query
WHERE body_tsv @@ query
ORDER BY rank DESC
LIMIT 20
;
If you need the next 20, don't just query with limit 40 and throw away the first 20, but use
"
OFFSET
20
LIMIT
20
" to return just the next 20.
To have some stability, so that the documents with same rank still come out in the same order
when using OFFSET 20, add a unique field (like id column of table articles) to ORDER BY in
both queries.
SELECT title, ts_rank_cd(body_tsv, query, 20) AS text_rank
FROM articles, plainto_tsquery('spicy potatoes') AS query
WHERE body_tsv @@ query
ORDER BY rank DESC, articles.id
OFFSET 20 LIMIT 20
;
An application requests all products for a branch office to run
a complex calculation over them
Try to do as much data analysis as possible inside the database.
