From: Tom Lane <tgl@sss.pgh.pa.us>
To: Ronaldlee Ejalu <ejaluronaldlee@gmail.com>
Cc: pgsql-admin@postgresql.org
Subject: Re: SQL State 42P01
Date: Wed, 10 Jun 2015 15:20:19 -0400
Message-ID: <21966.1433964019@sss.pgh.pa.us> (raw)
In-Reply-To: <CAGFFjRhLoDLBkhg5jCPR6okduT-7jzeDxS_1vCxgC+ofHH_heQ@mail.gmail.com>
References: <CAGFFjRhLoDLBkhg5jCPR6okduT-7jzeDxS_1vCxgC+ofHH_heQ@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-admin>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: tgl@sss.pgh.pa.us, ejaluronaldlee@gmail.com
Subject: Re: SQL State 42P01
In-Reply-To: <21966.1433964019@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox