I have a relatively small database that consists of 12 tables. 3 tables serve the majority content on one of my websites. Approximately 24 hours ago I ran a small script that would do nothing more than update/delete some rows (~10k) in these tables in quick sucession. This was a script that I had run before plenty of times and is just something I use to clean up unnecessary content within this table. After about an hour of running this script I was and still am hit with an unnecessarily high amount of request timeouts from heroku. Since I had not touched anything with the project specifically I concluded that something must be going on with the database. I then proceeded to kill all open database connections and found that the timeouts stopped. Again after 10 minutes or so timeouts would start again. Looking at the queries being executed I saw that everytime that timeouts would happen is when there were 4-5 long running queries. All of these queries are either
SELECT count(*) FROM mytable WHERE private=false
SELECT id FROM mytable WHERE private=FALSE LIMIT 1000 OFFSET 1XXXXX
If left untouched these queries take approximately 8 minutes to execute!
What I am wondering is what has happened that all of a sudden doing count(*) on ~391k rows will take 8 minutes?
Things I have done:
Vaccum/Analyze on all tables in the database SET statement_timeout='120s' # this seems to fix the timeouts but doesn't explain what happened Reindexed Table
Some statistics on the table:
Table Size: 2912 MB TOAST Size: 123 MB Indexes Size: 31 MB hitrate: 50% total_read = 4217 MB total_miss = 2076 MB
Results after running "EXPLAIN ANALYZE SELECT count(*) FROM mytable WHERE private=false"
Result after running "EXPLAIN ANALYZE SELECT count(*) FROM mytable" http://gyazo.com/510ce6c644d9a9dda197e5e3e154624c
Output to "\d question"
The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:
CREATE INDEX mytable_idx_private ON "mytable" ("private");
SELECT count(*) FROM mytable WHERE mytable.private = false