A simple shell script to redistribute, index and analyze table in Greenplum

Post date: Nov 01, 2014 3:57:51 PM

[gpadmin@sachi reports]$ cat a.sh

#!/bin/bash

set -e

set -u

dbconnect="psql -X --set ON_ERROR_STOP=on --set AUTOCOMMIT=off sachi"

$dbconnect<<SQL

\timing on

\qecho Altering the distribution keys .. Please wait

ALTER TABLE abc set DISTRIBUTED by (id);

\qecho

\qecho Creating Indexes on the tables .. Please wait

create index idx_abc_id on abc(id);

\qecho

\qecho Analyzing  tables .. Please wait

ANALYZE abc;

SQL

For example

[gpadmin@sachi]$ ./a.sh

Timing is on.

Altering the distribution keys .. Please wait

WARNING:  distribution policy of relation "abc" already set to (id)

HINT:  Use ALTER TABLE "abc" SET WITH (REORGANIZE=TRUE) DISTRIBUTED BY (id) to force redistribution

ALTER TABLE

Time: 3.435 ms

Creating Indexes on the tables .. Please wait

CREATE INDEX

Time: 155.207 ms

Analyzing tables .. Please wait

ANALYZE

Time: 55.675 ms

[gpadmin@sachi]$ 

You may want to display some more information about distribution keys before and after. Below is second version of the above script.

[gpadmin@sachi]$ cat a.sh

#!/bin/bash

set -e

set -u

dbconnect="psql -X --set ON_ERROR_STOP=on --set AUTOCOMMIT=off sachi"

$dbconnect<<SQL

\timing on

SELECT 'You are connected to database '|| current_database()||' as user '||current_user as Connection_details;

\qecho Distribution keys before update.....

select table_owner,table_name, COALESCE(distribution_keys,'DISTRIBUTED RANDOMLY') distribution_keys

 from

(

SELECT pgn.nspname as table_owner,

pgc.relname as table_name,

pga.attname as distribution_keys

FROM (SELECT gdp.localoid,

CASE

WHEN ( Array_upper(gdp.attrnums, 1) > 0 ) THEN

Unnest(gdp.attrnums)

ELSE NULL

END AS attnum

FROM gp_distribution_policy gdp

ORDER BY gdp.localoid) AS distrokey

INNER JOIN pg_class AS pgc

ON distrokey.localoid = pgc.oid

INNER JOIN pg_namespace pgn

ON pgc.relnamespace = pgn.oid

LEFT OUTER JOIN pg_attribute pga

ON distrokey.attnum = pga.attnum

AND distrokey.localoid = pga.attrelid

ORDER BY pgn.nspname,

pgc.relname) as a

where upper(a.table_owner)='PUBLIC'

and lower(a.table_name) in

('abc');

\qecho Altering the distribution keys .. Please wait

ALTER TABLE abc set DISTRIBUTED by (id);

\qecho

SELECT 'You are connected to database '|| current_database()||' as user '||current_user as Connection_details;

\qecho Distribution keys before update.....

select table_owner,table_name, COALESCE(distribution_keys,'DISTRIBUTED RANDOMLY') distribution_keys

 from

(

SELECT pgn.nspname as table_owner,

pgc.relname as table_name,

pga.attname as distribution_keys

FROM (SELECT gdp.localoid,

CASE

WHEN ( Array_upper(gdp.attrnums, 1) > 0 ) THEN

Unnest(gdp.attrnums)

ELSE NULL

END AS attnum

FROM gp_distribution_policy gdp

ORDER BY gdp.localoid) AS distrokey

INNER JOIN pg_class AS pgc

ON distrokey.localoid = pgc.oid

INNER JOIN pg_namespace pgn

ON pgc.relnamespace = pgn.oid

LEFT OUTER JOIN pg_attribute pga

ON distrokey.attnum = pga.attnum

AND distrokey.localoid = pga.attrelid

ORDER BY pgn.nspname,

pgc.relname) as a

where upper(a.table_owner)='PUBLIC'

and lower(a.table_name) in

('abc');

\qecho Creating Indexes on the tables .. Please wait

create index idx_abc_id on abc(id);

\qecho

\qecho Analyzing  tables .. Please wait

ANALYZE abc;

SQL

[gpadmin@sachi ]$ 

[gpadmin@sachi ]$ ./a.sh

Timing is on.

                 connection_details                  

-----------------------------------------------------

 You are connected to database sachi as user gpadmin

(1 row)

Time: 2.217 ms

Distribution keys before update.....

 table_owner | table_name | distribution_keys 

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

 public      | abc        | id

(1 row)

Time: 12.483 ms

Altering the distribution keys .. Please wait

WARNING:  distribution policy of relation "abc" already set to (id)

HINT:  Use ALTER TABLE "abc" SET WITH (REORGANIZE=TRUE) DISTRIBUTED BY (id) to force redistribution

ALTER TABLE

Time: 1.566 ms

                 connection_details                  

-----------------------------------------------------

 You are connected to database sachi as user gpadmin

(1 row)

Time: 0.743 ms

Distribution keys before update.....

 table_owner | table_name | distribution_keys 

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

 public      | abc        | id

(1 row)

Time: 5.513 ms

Creating Indexes on the tables .. Please wait

CREATE INDEX

Time: 141.049 ms

Analyzing tables .. Please wait

ANALYZE

Time: 54.354 ms

[gpadmin@sachi ]$