Table row counts in Greenplum

posted Apr 28, 2017, 10:37 AM by Sachchida Ojha   [ updated Apr 28, 2017, 10:37 AM ]
sachi=# CREATE TABLE sachitabletest (
sachi(#     id          integer,
sachi(#     gender     char(1),
sachi(#     title       varchar(40) NOT NULL,
sachi(#     did         integer NOT NULL,
sachi(#     date_prod   date,
sachi(#     kind        varchar(10),
sachi(#     len         interval hour to minute
sachi(# ) DISTRIBUTED BY (id,gender);
CREATE TABLE

sachi=# SELECT   oid, nspname AS schemaname,relname,reltuples
 FROM pg_class C LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
WHERE nspname NOT IN ('pg_catalog', 'information_schema') AND  relkind='r' 
and relname='sachitabletest'
ORDER BY reltuples DESC;
-[ RECORD 1 ]--------------
schemaname | public
relname    | sachitabletest
reltuples  | 0

sachi=# 
sachi=# insert into sachitabletest values(1,'M','Greenplum DBA','1',current_timestamp,'abc','3 hour');
INSERT 0 1
sachi=# 

sachi=# SELECT  N.oid, 
nspname AS schemaname,
relname,reltuples 
FROM pg_class C 
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace) 
 WHERE nspname NOT IN ('pg_catalog', 'information_schema') 
AND relkind='r' 
AND relname='sachitabletest' 
 ORDER BY reltuples DESC;
-[ RECORD 1 ]--------------
oid | 2200
schemaname | public
relname | sachitabletest
reltuples | 1

-- Finding no of rows in all tables

SELECT  N.oid, 
nspname AS schemaname,
relname,reltuples 
FROM pg_class C 
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace) 
 WHERE nspname NOT IN ('pg_catalog', 'information_schema') 
AND relkind='r' 
--AND relname='sachitabletest' 
 ORDER BY reltuples DESC;

-- Important PG_CATALOG functions

sachi=# \df pg*analyze*
List of functions
-[ RECORD 1 ]-------+----------------------------------
Schema              | pg_catalog
Name                | pg_stat_get_last_analyze_time
Result data type    | timestamp with time zone
Argument data types | oid
Type                | normal
-[ RECORD 2 ]-------+----------------------------------
Schema              | pg_catalog
Name                | pg_stat_get_last_autoanalyze_time
Result data type    | timestamp with time zone
Argument data types | oid
Type                | normal

sachi=# \df pg*tuples*
List of functions
-[ RECORD 1 ]-------+----------------------------
Schema              | pg_catalog
Name                | pg_stat_get_tuples_deleted
Result data type    | bigint
Argument data types | oid
Type                | normal
-[ RECORD 2 ]-------+----------------------------
Schema              | pg_catalog
Name                | pg_stat_get_tuples_fetched
Result data type    | bigint
Argument data types | oid
Type                | normal
-[ RECORD 3 ]-------+----------------------------
Schema              | pg_catalog
Name                | pg_stat_get_tuples_inserted
Result data type    | bigint
Argument data types | oid
Type                | normal
-[ RECORD 4 ]-------+----------------------------
Schema              | pg_catalog
Name                | pg_stat_get_tuples_returned
Result data type    | bigint
Argument data types | oid
Type                | normal
-[ RECORD 5 ]-------+----------------------------
Schema              | pg_catalog
Name                | pg_stat_get_tuples_updated
Result data type    | bigint
Argument data types | oid
Type                | normal

sachi=# \df pg*vacuum*
List of functions
-[ RECORD 1 ]-------+---------------------------------
Schema              | pg_catalog
Name                | pg_stat_get_last_autovacuum_time
Result data type    | timestamp with time zone
Argument data types | oid
Type                | normal
-[ RECORD 2 ]-------+---------------------------------
Schema              | pg_catalog
Name                | pg_stat_get_last_vacuum_time
Result data type    | timestamp with time zone
Argument data types | oid
Type                | normal

sachi=# 
Comments