[Solved] Select Count(*) increased from

EverSQL Database Performance Knowledge Base

Select Count(*) increased from

Database type:

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

or 

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"

http://gyazo.com/3c9ef2caa5ccd0ce523dd8ef4cfbf733

Result after running "EXPLAIN ANALYZE SELECT count(*) FROM mytable" http://gyazo.com/510ce6c644d9a9dda197e5e3e154624c

Output to "\d question"

http://pastebin.com/WAPecWdw

How to optimize this SQL query?

The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:

  1. Description of the steps you can take to speed up the query.
  2. The optimal indexes for this query, which you can copy and create in your database.
  3. An automatically re-written query you can copy and execute in your database.
The optimization process and recommendations:
  1. Create Optimal Indexes (modified query below): The recommended indexes are an integral part of this optimization effort and should be created before testing the execution duration of the optimized query.
Optimal indexes for this query:
CREATE INDEX mytable_idx_private ON "mytable" ("private");
The optimized query:
SELECT
        count(*) 
    FROM
        mytable 
    WHERE
        mytable.private = false

Related Articles



* original question posted on StackOverflow here.