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;

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

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

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;

--------+-------+------------+--------------+-------------+---------------+---------------+--------------+---------------+-------------

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 '|';"