Ronaldlee Ejalu <ejaluronaldlee@gmail.com> writes:
> SELECT relname
> , seq_scan-idx_scan AS too_much_seq
> , case when seq_scan-idx_scan>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='atg' AND pg_relation_size(relname::regclass)>80000
> /*AND relname NOT IN ('iml_returns'
> ,'purch_order'
> , 'rpt_dates'
> ,'paypal_analysis_validation'
> , 'ntv_data_mv_cte'
> ,'sg_atg_test','sg_atg_set','personalized_skul',
> 'month_sku_rollup', 'on_order_sku_rollup', 'back_order_sku')*/
> ORDER BY too_much_seq DESC;</Code>
> This checks to see if there are more sequence scans than index scan.
> When I execute the query it complains of the existing relations not being
> in existence with a SQL state:42P01.
"relname::regclass" is pretty much guaranteed not to work in any database
with multiple schemas, not to mention that it can mess up on table names
containing special characters such as periods. Use the table OID
(pg_stat_all_tables.relid) instead as the argument for pg_relation_size.
regards, tom lane