Обсуждение: SQL State 42P01
Hi all,
I have this query I am executing against a postgres database instance 9.3,
I have this query I am executing against a postgres database instance 9.3,
<Code>
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_scanFROM pg_stat_all_tablesWHERE 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.
Is there a way I can resolve this.
Thanks,
Ron
When I execute the query it complains of the existing relations not being in existence with a SQL state:42P01.
Is there a way I can resolve this.
Thanks,
Ron
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