PgHero
No long running queries
Number of connections healthy 26
Vacuuming healthy
No columns near integer overflow
No invalid indexes
No duplicate indexes
1 suggested index
7 slow queries

Suggested Indexes

Add indexes to speed up queries. Here’s a migration to help.


Details
CREATE INDEX CONCURRENTLY ON tweets (query_id, retweet_count)

to speed up

Total Time Average Time Calls
0 min 0.4% 27 ms 332 project_n
SELECT  "tweets".* FROM "tweets" WHERE "tweets"."query_id" = $1  ORDER BY "tweets"."retweet_count" DESC LIMIT ?

Slow Queries

Slow queries take 20 ms or more on average and have been called at least 100 times.

Explain queries to see where to add indexes.

Total Time Average Time Calls
6 min 18% 40 ms 8,746 project_n
SELECT t.relname AS table, ix.relname AS name, regexp_replace(pg_get_indexdef(indexrelid), ?, ?) AS columns, regexp_replace(pg_get_indexdef(indexrelid), ?, ?) AS using, indisunique AS unique, indisprimary AS primary, indisvalid AS valid, indexprs::text, indpred::text, pg_get_indexdef(indexrelid) AS definition FROM pg_index i INNER JOIN pg_class t ON t.oid = i.indrelid INNER JOIN pg_class ix ON ix.oid = i.indexrelid ORDER BY 1, 2
5 min 14% 71 ms 4,044 project_n
SELECT COUNT(*) FROM "tweets" WHERE "tweets"."query_id" = $1 AND "tweets"."retweet" = $2
4 min 11% 65 ms 3,378 project_n
SELECT  twitter_id, created_at, screen_name, user_image_url, user_followers_count, text, retweet_count, favorite_count, document FROM "tweets" WHERE "tweets"."query_id" = $1 AND "tweets"."retweet" = $2  ORDER BY created_at desc LIMIT ? OFFSET ?
1 min 2% 47 ms 904 project_n
SELECT schemaname AS schema, t.relname AS table, ix.relname AS name, regexp_replace(pg_get_indexdef(i.indexrelid), ?, ?) AS columns, regexp_replace(pg_get_indexdef(i.indexrelid), ?, ?) AS using, indisunique AS unique, indisprimary AS primary, indisvalid AS valid, indexprs::text, indpred::text, pg_get_indexdef(i.indexrelid) AS definition FROM pg_index i INNER JOIN pg_class t ON t.oid = i.indrelid INNER JOIN pg_class ix ON ix.oid = i.indexrelid LEFT JOIN pg_stat_user_indexes ui ON ui.indexrelid = i.indexrelid ORDER BY 1, 2
0 min 0.8% 63 ms 250 project_n
SELECT "tweets".* FROM "tweets" WHERE "tweets"."query_id" = $1 AND "tweets"."tweet_group_id" IS NULL  ORDER BY "tweets"."created_at" ASC
0 min 0.4% 27 ms 332 project_n
SELECT  "tweets".* FROM "tweets" WHERE "tweets"."query_id" = $1  ORDER BY "tweets"."retweet_count" DESC LIMIT ?
Details
CREATE INDEX CONCURRENTLY ON tweets (query_id, retweet_count)
0 min 0.4% 21 ms 409 project_n
SELECT n.nspname AS schema, c.relname AS name, CASE WHEN c.relkind = ? THEN ? ELSE ? END AS type, pg_size_pretty(pg_table_size(c.oid)) AS size, pg_table_size(c.oid) AS size_bytes FROM pg_class c LEFT JOIN pg_namespace n ON (n.oid = c.relnamespace) WHERE n.nspname NOT IN (?, ?) AND n.nspname !~ ? AND c.relkind IN (?, ?) ORDER BY pg_table_size(c.oid) DESC, name ASC