Postgresql rds: relation … does not exists

postgresql

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 psl 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.

Best Answer

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

Related Question