0

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.

3
  • Why use the convolution relname::regclass when the relid is directly available in the table?
    – jjanes
    Oct 12, 2020 at 19:33
  • What is the full error message? That is, what relation does it say does not exist?
    – jjanes
    Oct 12, 2020 at 19:37
  • 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 :(
    – Swoozie
    Oct 13, 2020 at 20:33

1 Answer 1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.