Relation does not exist while scanning schema

Question

My Postgres database is getting slow while executing some function. But another database of identical design and with more data is much faster than the first one. I have recently migrated data from MS SQL to PostgreSQL. So I have checked if there is any problem from following queries:

SELECT
  relname                                               AS TableName,
  to_char(seq_scan, '999,999,999,999')                  AS TotalSeqScan,
  to_char(idx_scan, '999,999,999,999')                  AS TotalIndexScan,
  to_char(n_live_tup, '999,999,999,999')                AS TableRows,
  pg_size_pretty(pg_relation_size(relname :: regclass)) AS TableSize
FROM pg_stat_all_tables
WHERE schemaname = 'loan'
      AND 50 * seq_scan > idx_scan -- more then 2%
      AND n_live_tup > 10000
      AND pg_relation_size(relname :: regclass) > 5000000
ORDER BY relname ASC;

And I got the result:

ERROR:  relation "mv_transaction_view" does not exist
SQL state: 42P01

What is the solution for this? I have a materialized view named mv_transaction_view.

Answer 1

Use relid instead of relname:

SELECT relname                                 AS tablename
     , to_char(seq_scan  , '999,999,999,999')  AS total_seqscan
     , to_char(idx_scan  , '999,999,999,999')  AS total_indexscan
     , to_char(n_live_tup, '999,999,999,999')  AS tablerows
     , pg_size_pretty(pg_relation_size(relid)) AS tablesize
FROM   pg_stat_all_tables
WHERE  schemaname = 'loan'
AND    50 * seq_scan > COALESCE(idx_scan, 0) -- more then 2%
AND    n_live_tup > 10000
AND    pg_relation_size(relid) > 5000000;

Why?

Assuming you do not have the schema loan in your search_path, Postgres is unable to resolve the cast mv_transaction_view::regclass - looking up unqualified tablenames only in the schemas listed in the current search_path. Hence the error:

 ERROR: relation "mv_transaction_view" does not exist

Materialized views are also listed among "tables" in the view pg_stat_all_tables, that's not the problem.

The missing schema in the search_path is also not the problem. Just the messenger in a manner of speaking. The problem is a bug in your query. Imagine a table loan.tbl and a search_path of public, loan. Your query would just work fine. Or would it?
Now imagine an innocent (or malevolent) user creating a table public.tbl. Then you run your query again ...

Using relid instead is the safe solution. You don't even need to cast since that's the OID of a table, exactly what pg_relation_size() expects. (The cast oid -> regclass is implicit.)

Details:

• How to check if a table exists in a given schema
• How does the search_path influence identifier resolution and the “current schema”

Also note COALESCE(idx_scan, 0):
idx_scan can be NULL, and you wouldn't wanna miss tables with all sequential scans.

• The manual on pg_stat_all_tables.