PgHero
1 long running query
Connections healthy 59
Vacuuming healthy
No columns near integer overflow (3 unreadable sequences)
No invalid indexes or constraints
1 duplicate index
2 suggested indexes
16 slow queries

Unreadable Sequences

This is likely due to missing privileges. Make sure your user has the SELECT privilege for each sequence.

Column Sequence
subscription.id Unable to parse: nextval((pg_get_serial_sequence('public.subscription'::text, 'id'::text))::regclass)
configuration.id Unable to parse: nextval((pg_get_serial_sequence('public.configuration'::text, 'id'::text))::regclass)
singlepayment.id Unable to parse: nextval((pg_get_serial_sequence('public.singlepayment'::text, 'id'::text))::regclass)

Long Running Queries

We recommend setting a statement timeout on all non-superusers with:

ALTER ROLE <user> SET statement_timeout TO '60s';
Pid Duration State
132066 00:09:45 idle in transaction
postgres 

        SELECT config AS config
        FROM upload
        WHERE email IS NULL AND created_at < NOW() - INTERVAL '4 hours'
        AND created_at > NOW() - INTERVAL '1 days'

Duplicate Indexes

These indexes exist, but aren’t needed. Remove them for faster writes. 

rails generate migration remove_unneeded_indexes

And paste

remove_index :upload, name: "upload_task_id_idx1", column: :task_id
Details
On upload 
upload_task_id_idx1 (task_id)
is covered by 
upload_task_id_idx (task_id)

Suggested Indexes

Add indexes to speed up queries. 

rails generate migration add_suggested_indexes

And paste

commit_db_transaction
add_index :configuration, [:user_id], algorithm: :concurrently
add_index :upload, [:created_at], algorithm: :concurrently
Details
CREATE INDEX CONCURRENTLY ON configuration (user_id)
 
Rows: 78010
Row progression: 78010, 2

Row estimates
- user_id (=): 2

Existing indexes
- id PRIMARY

to speed up

Total Time Average Time Calls
1 min < 0.1% 35 ms 867 postgres 
SELECT "name","user_id","settings","id","upload_type","description" FROM "configuration" WHERE "user_id"=$1
0 min < 0.1% 35 ms 834 postgres 
SELECT "id","settings","description","user_id","name","upload_type" FROM "configuration" WHERE "user_id"=$1
0 min < 0.1% 35 ms 828 postgres 
SELECT "upload_type","user_id","id","name","settings","description" FROM "configuration" WHERE "user_id"=$1
0 min < 0.1% 35 ms 772 postgres 
SELECT "settings","upload_type","description","name","user_id","id" FROM "configuration" WHERE "user_id"=$1
0 min < 0.1% 35 ms 777 postgres 
SELECT "description","settings","id","upload_type","user_id","name" FROM "configuration" WHERE "user_id"=$1
0 min < 0.1% 35 ms 597 postgres 
SELECT "settings","upload_type","name","id","user_id","description" FROM "configuration" WHERE "user_id"=$1
0 min < 0.1% 38 ms 292 postgres 
SELECT "id","description","settings","user_id","name","upload_type" FROM "configuration" WHERE "user_id"=$1
0 min < 0.1% 37 ms 278 postgres 
SELECT "description","id","settings","name","user_id","upload_type" FROM "configuration" WHERE "user_id"=$1
0 min < 0.1% 39 ms 240 postgres 
SELECT "upload_type","description","settings","name","id","user_id" FROM "configuration" WHERE "user_id"=$1
0 min < 0.1% 38 ms 108 postgres 
SELECT "upload_type","description","user_id","settings","name","id" FROM "configuration" WHERE "user_id"=$1
0 min < 0.1% 37 ms 51 postgres 
SELECT "user_id","id","settings","upload_type","name","description" FROM "configuration" WHERE "user_id"=$1
Details
CREATE INDEX CONCURRENTLY ON upload (created_at)
 
Rows: 11452388
Row progression: 11452388, 1145239

Row estimates
- created_at (>): 1145239
- created_at (<): 1145239
- email (null): 2571061

Existing indexes
- id PRIMARY
- task_id
- task_id
- user_id, created_at
- user_id, created_at DESC

to speed up

Total Time Average Time Calls
6,612 min 94% 261,162 ms 1,519 postgres 
SELECT config AS config
        FROM upload
        WHERE email IS NULL AND created_at < NOW() - INTERVAL $1
        AND created_at > NOW() - INTERVAL $2

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,612 min 94% 261,162 ms 1,519 postgres 
SELECT config AS config
        FROM upload
        WHERE email IS NULL AND created_at < NOW() - INTERVAL $1
        AND created_at > NOW() - INTERVAL $2
Details
CREATE INDEX CONCURRENTLY ON upload (created_at)
 
Rows: 11452388
Row progression: 11452388, 1145239

Row estimates
- created_at (>): 1145239
- created_at (<): 1145239
- email (null): 2571061

Existing indexes
- id PRIMARY
- task_id
- task_id
- user_id, created_at
- user_id, created_at DESC
32 min 0.5% 23 ms 84,180 postgres 
UPDATE "upload" SET "user_id"=$1,"email"=$2 WHERE "id"=$3
Covered by index on (id)
Rows: 11452388
Row progression: 11452388, 1

Row estimates
- id (=): 1

Existing indexes
- id PRIMARY
- task_id
- task_id
- user_id, created_at
- user_id, created_at DESC
17 min 0.2% 785 ms 1,333 postgres 
DELETE FROM "user" WHERE "id"=$1
Covered by index on (id)
Rows: 1140905
Row progression: 1140905, 1

Row estimates
- id (=): 1

Existing indexes
- id PRIMARY
- auth_id
- email
16 min 0.2% 714 ms 1,332 admin 
DELETE FROM ONLY "public"."files" WHERE $1 OPERATOR(pg_catalog.=) "user_id"
14 min 0.2% 544 ms 1,600 postgres 
SELECT CASE WHEN $3 < LENGTH(CAST("public"."upload"."audio_samples" AS TEXT)) THEN $4 ELSE "public"."upload"."audio_samples" END AS "audio_samples", CASE WHEN $5 < LENGTH(CAST("public"."upload"."export_links" AS TEXT)) THEN $6 ELSE "public"."upload"."export_links" END AS "export_links", CASE WHEN $7 < LENGTH(CAST("public"."upload"."edits" AS TEXT)) THEN $8 ELSE "public"."upload"."edits" END AS "edits", CASE WHEN $9 < LENGTH(CAST("public"."upload"."config" AS TEXT)) THEN $10 ELSE "public"."upload"."config" END AS "config", CASE WHEN $11 < LENGTH(CAST("public"."upload"."timeline_links" AS TEXT)) THEN $12 ELSE "public"."upload"."timeline_links" END AS "timeline_links", CASE WHEN $13 < LENGTH(CAST("public"."upload"."statistics" AS TEXT)) THEN $14 ELSE "public"."upload"."statistics" END AS "statistics", CASE WHEN $15 < LENGTH(CAST("public"."upload"."audio_links" AS TEXT)) THEN $16 ELSE "public"."upload"."audio_links" END AS "audio_links" FROM "public"."upload" INNER JOIN ((SELECT "public"."upload"."id" FROM "public"."upload" ORDER BY "public"."upload"."id" ASC LIMIT $1) UNION ALL (SELECT "public"."upload"."id" FROM "public"."upload" ORDER BY "public"."upload"."id" DESC LIMIT $2)) AS "result" ON ("result"."id" = "public"."upload"."id")
1 min < 0.1% 36 ms 1,332 admin 
DELETE FROM ONLY "public"."configuration" WHERE $1 OPERATOR(pg_catalog.=) "user_id"
1 min < 0.1% 35 ms 867 postgres 
SELECT "name","user_id","settings","id","upload_type","description" FROM "configuration" WHERE "user_id"=$1
Details
CREATE INDEX CONCURRENTLY ON configuration (user_id)
 
Rows: 78010
Row progression: 78010, 2

Row estimates
- user_id (=): 2

Existing indexes
- id PRIMARY
0 min < 0.1% 35 ms 834 postgres 
SELECT "id","settings","description","user_id","name","upload_type" FROM "configuration" WHERE "user_id"=$1
Details
CREATE INDEX CONCURRENTLY ON configuration (user_id)
 
Rows: 78010
Row progression: 78010, 2

Row estimates
- user_id (=): 2

Existing indexes
- id PRIMARY
0 min < 0.1% 35 ms 828 postgres 
SELECT "upload_type","user_id","id","name","settings","description" FROM "configuration" WHERE "user_id"=$1
Details
CREATE INDEX CONCURRENTLY ON configuration (user_id)
 
Rows: 78010
Row progression: 78010, 2

Row estimates
- user_id (=): 2

Existing indexes
- id PRIMARY
0 min < 0.1% 35 ms 772 postgres 
SELECT "settings","upload_type","description","name","user_id","id" FROM "configuration" WHERE "user_id"=$1
Details
CREATE INDEX CONCURRENTLY ON configuration (user_id)
 
Rows: 78010
Row progression: 78010, 2

Row estimates
- user_id (=): 2

Existing indexes
- id PRIMARY
0 min < 0.1% 35 ms 777 postgres 
SELECT "description","settings","id","upload_type","user_id","name" FROM "configuration" WHERE "user_id"=$1
Details
CREATE INDEX CONCURRENTLY ON configuration (user_id)
 
Rows: 78010
Row progression: 78010, 2

Row estimates
- user_id (=): 2

Existing indexes
- id PRIMARY
0 min < 0.1% 35 ms 597 postgres 
SELECT "settings","upload_type","name","id","user_id","description" FROM "configuration" WHERE "user_id"=$1
Details
CREATE INDEX CONCURRENTLY ON configuration (user_id)
 
Rows: 78010
Row progression: 78010, 2

Row estimates
- user_id (=): 2

Existing indexes
- id PRIMARY
0 min < 0.1% 38 ms 292 postgres 
SELECT "id","description","settings","user_id","name","upload_type" FROM "configuration" WHERE "user_id"=$1
Details
CREATE INDEX CONCURRENTLY ON configuration (user_id)
 
Rows: 78010
Row progression: 78010, 2

Row estimates
- user_id (=): 2

Existing indexes
- id PRIMARY
0 min < 0.1% 37 ms 278 postgres 
SELECT "description","id","settings","name","user_id","upload_type" FROM "configuration" WHERE "user_id"=$1
Details
CREATE INDEX CONCURRENTLY ON configuration (user_id)
 
Rows: 78010
Row progression: 78010, 2

Row estimates
- user_id (=): 2

Existing indexes
- id PRIMARY
0 min < 0.1% 39 ms 240 postgres 
SELECT "upload_type","description","settings","name","id","user_id" FROM "configuration" WHERE "user_id"=$1
Details
CREATE INDEX CONCURRENTLY ON configuration (user_id)
 
Rows: 78010
Row progression: 78010, 2

Row estimates
- user_id (=): 2

Existing indexes
- id PRIMARY
0 min < 0.1% 38 ms 108 postgres 
SELECT "upload_type","description","user_id","settings","name","id" FROM "configuration" WHERE "user_id"=$1
Details
CREATE INDEX CONCURRENTLY ON configuration (user_id)
 
Rows: 78010
Row progression: 78010, 2

Row estimates
- user_id (=): 2

Existing indexes
- id PRIMARY