pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  

help / color / mirror / Atom feed

SQL State 42P01
2+ messages / 2 participants
[nested] [flat]

* SQL State 42P01
@ 2015-06-10 18:49  Ronaldlee Ejalu <ejaluronaldlee@gmail.com>
  0 siblings, 1 reply; 2+ messages in thread

From: Ronaldlee Ejalu @ 2015-06-10 18:49 UTC (permalink / raw)
  To: pgsql-admin

Hi all,

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_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.

Is there a way I can resolve this.

Thanks,

Ron

^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: SQL State 42P01
@ 2015-06-10 19:20  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Ronaldlee Ejalu <ejaluronaldlee@gmail.com>
  0 siblings, 0 replies; 2+ messages in thread

From: Tom Lane @ 2015-06-10 19:20 UTC (permalink / raw)
  To: Ronaldlee Ejalu <ejaluronaldlee@gmail.com>; +Cc: pgsql-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



^ permalink  raw  reply  [nested|flat] 2+ messages in thread

end of thread, other threads:[~2015-06-10 19:20 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2015-06-10 18:49 SQL State 42P01 Ronaldlee Ejalu <ejaluronaldlee@gmail.com>
2015-06-10 19:20 ` Tom Lane <tgl@sss.pgh.pa.us>

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