[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 [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 ]$ |