Postgresql rds: relation .... does not exists

Question

This seems to be a question that is asked alot all of the answers that I have read do not seem to pertain to this situation. I maybe wrong, I am a newbie but here is the question.

I am using psql 11 in amazon rds. I have one db on the instance with 2 schemas. It is running slow in testing so I was going to run the missing index query.

SELECT
 relname,
 seq_scan - idx_scan AS too_much_seq,
 CASE 
 WHEN seq_scan - coalesce(idx_scan, 0) > 0
 THEN 'Missing Index?'
 ELSE 'OK'
 END,
 pg_relation_size(relname::regclass) AS rel_size,
 seq_scan,
 idx_scan
 FROM pg_stat_all_tables
 WHERE
 schemaname IN('schemaname') AND
 pg_relation_size(relname::regclass) > 80000
 ORDER BY too_much_seq DESC;

When I run it in this new environment I am getting a relation does not exists.

I have checked permissions, ownership, lowercase, I can see the table, run queries against the table, however I can't seem to get this query to work. If I do a SELECT * from pg_stat_all_tables I can see all of the tables that do not exists in he other query.

I am not sure what is wrong. I am pretty sure it is in the pg_relation_size(relname::regclass) section, I think that is where it is not finding the relation, however I have been unable to find documentation on this function that would appear to shed light on my issue.

Any assistance would be appreciated.

• jjanes: Why use the convolution relname::regclass when the relid is directly available in the table?
• jjanes: What is the full error message? That is, what relation does it say does not exist?
• Swoozie: because being a silly newbie, I just used the query I was provided and didnt know about the relid. all of the google searches I came up will all showed the relname :(

Answer 1

jjanes answered the question in his comment.

Why use the convolution relname::regclass when the relid is directly available in the table?

using the relid fixed the issue