Jeff Chen <jeff@xxxxxxxxxxxxxx> 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 -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
