Helpful SQL queries for Greenplum DBA's

1. Viewing table data distribution across segment Servers
To view the data distribution of a table’s rows (the number of rows on each segment), you can run a query such as:

[gpadmin@sachi ~]$ SELECT gp_segment_id, count(*) FROM abc GROUP BY gp_segment_id; 

To see the data distribution of a table’s rows on segment servers and the directory location of where the data is located, you can run a query such as: 

[gpadmin@sachi ~]$SELECT table_name.gp_segment_id,hostname, fselocation as datadir,count(*) 
FROM table_name, pg_filespace_entry pgfse,gp_segment_configuration gsc
WHERE gsc.dbid=pgfse.fsedbid and table_name.gp_segment_id= pgfse.fsedbid 
GROUP BY table_name.gp_segment_id,hostname,datadir
ORDER BY gp_segment_id;
The gadget spec URL could not be found
Balanced Distribution: A table is considered to have a balanced distribution if all of the segments have roughly the same number of rows.

2. Viewing the List of Databases

SELECT datname from pg_database;

3. Viewing Existing Tablespaces and Filespaces

SELECT spcname as tblspc, fsname as filespc,
fsedbid as seg_dbid, fselocation as datadir
FROM pg_tablespace pgts, pg_filespace pgfs,
pg_filespace_entry pgfse
WHERE pgts.spcfsoid=pgfse.fsefsoid
AND pgfse.fsefsoid=pgfs.oid
ORDER BY tblspc, seg_dbid;

4. Viewing the Current Schema
The gadget spec URL could not be found
SELECT current_schema();
Use the SHOW command to view the current search path. For example:
SHOW search_path;

5. Viewing Your Partition Design

SELECT partitionboundary, partitiontablename, partitionname, partitionlevel, partitionrank 
FROM pg_partitions 
WHERE tablename='table_name';

6. Shows how many external table files are permitted per external table

SELECT * FROM pg_max_external_files;

7. Information about the session holding or waiting to hold a lock

SELECT locktype, database, c.relname, l.relation, l.transactionid, l.transaction, l.pid, l.mode, l.granted, a.current_query
FROM pg_locks l, pg_class c, pg_stat_activity a WHERE l.relation=c.oid AND l.pid=a.procpid
ORDER BY c.relname;

8. Information about the bloat
The gadget spec URL could not be found
sachi=# select
sachi-# current_database() as dbname
sachi-# , relid
sachi-# , schemaname
sachi-# , tablename
sachi-# , round(bloat,1) as bloat_ratio
sachi-# , pg_size_pretty(expbytes) as expected_size
sachi-# , pg_size_pretty(relbytes) as relation_size
sachi-# , pg_size_pretty(wastedbytes) as wasted_space
sachi-# , round(1.0 * relbytes / nullif((sum(relbytes) over ())::bigint,0),4) as relative_size
sachi-# , round((bloat-1) * (1.0 * relbytes / nullif((sum(relbytes) over ())::bigint, 0)), 8) as concern
sachi-# from
sachi-# (
sachi(# SELECT
sachi(# relid
sachi(# , schemaname
sachi(# , tablename
sachi(# , CASE WHEN otta=0 THEN
sachi(# 0.0
sachi(# ELSE
sachi(# sml.relpages/otta::numeric
sachi(# END AS bloat
sachi(# , reltuples::bigint
sachi(# , relpages::bigint
sachi(# , otta
sachi(# , (bs*otta)::bigint as expbytes
sachi(# , CASE WHEN relpages < otta THEN 0 ELSE
sachi(# (bs*(sml.relpages-otta))::bigint
sachi(# END AS wastedbytes
sachi(# , CASE WHEN relpages < otta THEN 0 ELSE relpages::bigint - otta END AS wastedpages
sachi(# , (bs*relpages)::bigint as relbytes
sachi(# FROM
sachi(# (
sachi(# SELECT
sachi(# schemaname
sachi(# , tablename
sachi(# , cc.oid as relid
sachi(# , cc.reltuples
sachi(# , cc.relpages
sachi(# , bs
sachi(# , CEIL((cc.reltuples*((datahdr+ma-(CASE WHEN datahdr%ma=0 THEN ma ELSE datahdr%ma END))+nullhdr2+4))/(bs-20::float)) AS otta
sachi(# FROM
sachi(# (
sachi(# SELECT
sachi(# ma
sachi(# , bs
sachi(# , schemaname
sachi(# , tablename
sachi(# , (datawidth+(hdr+ma-(case when hdr%ma=0 THEN ma ELSE hdr%ma END)))::numeric AS datahdr
sachi(# , (maxfracsum*(nullhdr+ma-(case when nullhdr%ma=0 THEN ma ELSE nullhdr%ma END))) AS nullhdr2
sachi(# FROM
sachi(# (
sachi(# SELECT
sachi(# schemaname
sachi(# , tablename
sachi(# , hdr
sachi(# , ma
sachi(# , bs
sachi(# , SUM((1-s.null_frac)*avg_width) AS datawidth
sachi(# , MAX(s.null_frac) AS maxfracsum
sachi(# , hdr+(1+(count(case when s.null_frac<>0 then 1 end))/8) as nullhdr
sachi(# FROM
sachi(# pg_stats s
sachi(# cross join
sachi(# (
sachi(# SELECT
sachi(# current_setting('block_size')::numeric AS bs
sachi(# , CASE WHEN substring(version(),12,3) IN ('8.0','8.1','8.2') THEN 27 ELSE 23 END AS hdr
sachi(# , CASE WHEN version() ~ 'mingw32' THEN 8 ELSE 4 END AS ma
sachi(# ) AS constants
sachi(# GROUP BY schemaname, tablename, hdr, ma, bs
sachi(# ) AS foo
sachi(# ) AS rs
sachi(# JOIN pg_class cc
sachi(# ON
sachi(# cc.relname = rs.tablename
sachi(# and cc.relkind = 'r'
sachi(# JOIN pg_namespace nn
sachi(# ON
sachi(# cc.relnamespace = nn.oid
sachi(# AND nn.nspname = rs.schemaname
sachi(# ) AS sml
sachi(# ) wrapper
sachi-# where wastedbytes > 2*1024*1024 and bloat >= 1.4
sachi-# order by (bloat-1) * (1.0 * relbytes / nullif((sum(relbytes) over ())::bigint, 0)) desc;

dbname | relid | schemaname | tablename | bloat_ratio | expected_size | relation_size | wasted_space | relative_size | concern
--------+-------+------------+--------------+-------------+---------------+---------------+--------------+---------------+-------------
sachi | 83678 | public | bloattest | 19.6 | 67 MB | 1312 MB | 1245 MB | 0.9967 | 18.52354263
sachi | 1249 | pg_catalog | pg_attribute | 2.6 | 1696 kB | 4416 kB | 2720 kB | 0.0033 | 0.00525366
(2 rows)

8. Table and Index size

sachi=# SELECT tabs.nspname AS schema_name
sachi-# , COALESCE(parts.tablename, tabs.relname) AS table_name
sachi-# , ROUND(SUM(sotaidtablesize)/1024/1024/1024,3) AS table_GB
sachi-# , ROUND(SUM(sotaididxsize)/1024/1024/1024,3) AS index_GB
sachi-# FROM gp_toolkit.gp_size_of_table_and_indexes_disk sotd
sachi-# , (SELECT c.oid, c.relname, n.nspname
sachi(# FROM pg_class c
sachi(# , pg_namespace n
sachi(# WHERE n.oid = c.relnamespace
sachi(# AND c.relname NOT LIKE '%_err'
sachi(# )tabs
sachi-# LEFT JOIN pg_partitions parts
sachi-# ON tabs.nspname = parts.schemaname
sachi-# AND tabs.relname = parts.partitiontablename
sachi-# WHERE sotd.sotaidoid = tabs.oid and tabs.nspname = 'public'
sachi-# GROUP BY tabs.nspname, COALESCE(parts.tablename, tabs.relname)
sachi-# ORDER BY 1 desc,2;
schema_name | table_name | table_gb | index_gb
-------------+----------------------+----------+----------
public | abc | 0.000 | 0.000
public | bloattest | 1.281 | 0.000
public | cms | 0.000 | 0.000
public | cms_part | 0.000 | 0.000
public | cms_qlz | 0.002 | 0.000
public | cms_zlib | 0.002 | 0.000
public | cms_zlib9 | 0.002 | 0.000
public | err_earthquakes | 0.000 | 0.000
public | foo | 0.000 | 0.000
public | foo1 | 0.000 | 0.000
public | sachi | 0.000 | 0.000
public | sachi3 | 0.000 | 0.000
public | sales | 0.012 | 0.000
public | test | 0.000 | 0.000
public | usha | 0.000 | 0.000
public | usha1 | 0.000 | 0.000
public | usha3 | 0.000 | 0.000
public | wwearthquakes_lastwk | 0.001 | 0.000
(18 rows)
;

9. Table storage view
sachi=# CREATE OR REPLACE VIEW public.v_gp_table_storage AS
sachi-# SELECT current_timestamp AS tms, n.nspname AS schema_name, c.relname AS table_name,
sachi-# CASE
sachi-# WHEN c.relstorage = 'a'::"char" THEN 'row append-only'::text
sachi-# WHEN c.relstorage = 'c'::"char" THEN 'column append-only'::text
sachi-# WHEN c.relstorage = 'h'::"char" THEN 'heap'::text
sachi-# WHEN c.relstorage = 'x'::"char" THEN 'external'::text
sachi-# ELSE NULL::text
sachi-# END AS storage_type,
sachi-# a.compresstype AS compr_type,
sachi-# a.compresslevel AS compr_level,
sachi-# sotailtablesizedisk as tabind_sz,
sachi-# (sotailtablesizedisk / 1024^3)::numeric(20,2) as tabind_sz_gb,
sachi-# (sotailtablesizeuncompressed / 1024^3)::numeric(20,2) as tabind_sz_unc_gb,
sachi-# case WHEN (sotailtablesizedisk=0 or sotailtablesizedisk is null) THEN -1 ELSE (sotailtablesizeuncompressed/sotailtablesizedisk)::numeric(6,1) END as compr_ratio
sachi-# , c.relhassubclass as is_partitioned
sachi-# FROM pg_class c
sachi-# LEFT JOIN pg_appendonly a ON c.oid = a.relid
sachi-# LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
sachi-# LEFT JOIN gp_toolkit.gp_size_of_table_and_indexes_licensing sot ON sot.sotailoid = c.oid
sachi-# WHERE (n.nspname <> ALL (ARRAY['information_schema'::name, 'pg_catalog'::name, 'pg_toast'::name, 'gp_toolkit'::name])) AND c.relkind = 'r'::"char"
sachi-# ;
CREATE VIEW

10. Table storage view

sachi=# CREATE OR REPLACE VIEW public.v_gp_table_storage2 AS
sachi-# SELECT tms,
sachi-# schema_name,
sachi-# regexp_replace(table_name::text, '_1_prt_.*#39;::text, ''::text) AS table_name,
sachi-# storage_type,
sachi-# compr_type,
sachi-# compr_level,
sachi-# count(*) AS nr_of_partitions,
sachi-# sum(tabind_sz) AS tabind_size,
sachi-# sum(tabind_sz_gb) AS tabind_sz_gb,
sachi-# sum(tabind_sz_unc_gb) AS tabind_sz_unc_gb,
sachi-# round(avg(compr_ratio)::numeric, 2) AS avg_compr_ratio
sachi-# FROM public.v_gp_table_storage
sachi-# WHERE storage_type <> 'external'::text AND table_name !~~ 'err_%'::text AND not is_partitioned
sachi-# AND (COMPR_TYPE IS NOT NULL OR TABLE_NAME='cms')
sachi-# GROUP BY tms, schema_name, regexp_replace(table_name::text, '_1_prt_.*#39;::text, ''::text), storage_type, compr_type, compr_level
sachi-# ORDER BY 3;
CREATE VIEW


11. Find full query running in the database

 sachi=#psql -c "copy (select * from pg_stat_activity) to stdout CSV header delimiter '|';"

Comments