Received: from malur.postgresql.org ([217.196.149.56])
 by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from
 <pgsql-admin-owner+M44145=pgsql+2Dadmin=arkaria.postgresql.org@postgresql.org>)
 id 1Z2lYA-0000KY-4O
 for pgsql-admin@arkaria.postgresql.org; Wed, 10 Jun 2015 19:20:34 +0000
Received: from localhost ([127.0.0.1] helo=postgresql.org)
 by malur.postgresql.org with smtp (Exim 4.80) (envelope-from
 <pgsql-admin-owner+M44145=pgsql+2Dadmin=arkaria.postgresql.org@postgresql.org>)
 id 1Z2lY9-0002Vt-79
 for pgsql-admin@arkaria.postgresql.org; Wed, 10 Jun 2015 19:20:33 +0000
Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29])
 by malur.postgresql.org with esmtps (TLS1.2:RSA_AES_256_CBC_SHA256:256)
 (Exim 4.80) (envelope-from <tgl@sss.pgh.pa.us>) id 1Z2lY8-0002Vm-7E
 for pgsql-admin@postgresql.org; Wed, 10 Jun 2015 19:20:32 +0000
Received: from sss.pgh.pa.us ([66.207.139.130])
 by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256)
 (Exim 4.84) (envelope-from <tgl@sss.pgh.pa.us>) id 1Z2lY4-0001Bw-TN
 for pgsql-admin@postgresql.org; Wed, 10 Jun 2015 19:20:31 +0000
Received: from sss1.sss.pgh.pa.us (localhost [127.0.0.1])
 by sss.pgh.pa.us (8.14.4/8.14.4) with ESMTP id t5AJKJo1021967;
 Wed, 10 Jun 2015 15:20:19 -0400
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Ronaldlee Ejalu <ejaluronaldlee@gmail.com>
cc: pgsql-admin@postgresql.org
Subject: Re: SQL State 42P01
In-reply-to: 
 <CAGFFjRhLoDLBkhg5jCPR6okduT-7jzeDxS_1vCxgC+ofHH_heQ@mail.gmail.com>
References: 
 <CAGFFjRhLoDLBkhg5jCPR6okduT-7jzeDxS_1vCxgC+ofHH_heQ@mail.gmail.com>
Comments: In-reply-to Ronaldlee Ejalu <ejaluronaldlee@gmail.com>
 message dated "Wed, 10 Jun 2015 14:49:43 -0400"
Date: Wed, 10 Jun 2015 15:20:19 -0400
Message-ID: <21966.1433964019@sss.pgh.pa.us>
X-Pg-Spam-Score: -1.9 (-)
List-Archive: <http://archives.postgresql.org/pgsql-admin>
List-Help: <mailto:majordomo@postgresql.org?body=help>
List-ID: <pgsql-admin.postgresql.org>
List-Owner: <mailto:pgsql-admin-owner@postgresql.org>
List-Post: <mailto:pgsql-admin@postgresql.org>
List-Subscribe: <mailto:majordomo@postgresql.org?body=sub%20pgsql-admin>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-admin>
X-Mailing-List: pgsql-admin
Precedence: bulk
Sender: pgsql-admin-owner@postgresql.org

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


-- 
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin