Jeff Chen <jeff@pixleeteam.com> wrote:
> One of these queries that should be targeting something like 300K
> photos takes 38 seconds to run (with an aggregate/nested loop
> taking effectively all of that time),
With the seek time of commodity disk drives typically being 9ms, a
naive approach using random access to join to 300k rows on a single
thread with no caching would take 45 minutes; so the fact that you
are seeing much better than that implies some benefit from cache,
some sequential scanning, faster drives, or concurrent access to
multiple spindles.
> and then upon second execution with a warm cache, 4 seconds.
This shows that it is faster to access data in RAM than on disk,
and that your data wasn't already all in cache (most likely because
it doesn't all fit in RAM).
> Also worryingly, it spikes read IOPS to almost 1500/sec during
> the time and write IOPS 200/sec. When not running the query,
> steady level read iops basically nil, write hovers around 50-100.
The reads are just another symptom of not having the data fully
cached. The writes are more interesting. The two obvious
possibilities are that the query needed to use work files (for
sorts or hash tables) or that you were accessing a fair amount of
data which had not been vacuumed since it was last modified. To
help improve performance for the first, you might want to consider
increasing work_mem (although this will reduce RAM available for
caching). To improve performance for the second you might want to
make autovacuum more aggressive.
To get more specific advice, you may want to read this page and
follow the advice there:
https://wiki.postgresql.org/wiki/SlowQueryQuestions
--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
