Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
-- [30599] debug = True
-- [30599] db_host =
-- [30599] Redshift Column Encoding Utility Configuration
-- [30599] Created Cloudwatch Emitter in us-east-1
-- [30599] Connect [30599]
-- [30599] set statement_timeout = '1200000'
-- [30599] [30599] Running set statement_timeout = '1200000'
-- [30599] Success.
-- [30599] set application_name to 'ColumnEncodingUtility-v.9.3.4'
-- [30599] [30599] Running set application_name to 'ColumnEncodingUtility-v.9.3.4'
-- [30599] Success.
-- [30599] Connected to as master
-- [30599] Analyzing Table 'rankings' for Columnar Encoding Optimisations with 1 Threads...
/* [30599]
select pgn.nspname::text as schema, trim(a.name) as table, b.mbytes, a.rows, decode(pgc.reldiststyle,0,'EVEN',1,'KEY',8,'ALL') dist_style, TRIM(pgu.usename) "owner", pgd.description
from (select db_id, id, name, sum(rows) as rows from stv_tbl_perm a group by db_id, id, name) as a
join pg_class as pgc on pgc.oid = a.id
left outer join pg_description pgd ON pgd.objoid = pgc.oid and pgd.objsubid = 0
join pg_namespace as pgn on pgn.oid = pgc.relnamespace
join pg_user pgu on pgu.usesysid = pgc.relowner
join (select tbl, count(*) as mbytes
from stv_blocklist group by tbl) b on a.id=b.tbl
and pgn.nspname::text ~ 'public' and pgc.relname in ('rankings')
*/
-- [30599] Analyzing 1 table(s) which contain allocated data blocks
-- [30599] [u'public', u'rankings', 6380, 951105, None, u'beaverd_master', None]
/* [30599]
select /* getting count of raw columns in table */ count(9) count_raw_columns
from pg_table_def
where schemaname = 'public'
and lower(encoding) in ('raw','none')
and sortkey != 1
and tablename = 'rankings'
*/
-- [30599] Table public.rankings contains 28 unoptimised columns
-- [30599] analyze compression public."rankings"
-- [30599] Analyzing Table 'public.rankings'
/* [30599]
select /* fetching column descriptions for table */ "column", type, encoding, distkey, sortkey, "notnull", ad.adsrc
from pg_table_def de, pg_attribute at LEFT JOIN pg_attrdef ad ON (at.attrelid, at.attnum) = (ad.adrelid, ad.adnum)
where de.schemaname = 'public'
and de.tablename = 'rankings'
and at.attrelid = 'public."rankings"'::regclass
and de.column = at.attname
*/
-- [30599] Table Description: [u'attempt_id', u'character varying(256)', u'zstd', False, 0, False, None]
-- [30599] Table Description: [u'time_taken', u'timestamp without time zone', u'az64', False, 0, False, None]
-- [30599] Table Description: [u'test_id', u'character varying(256)', u'zstd', False, 0, False, None]
-- [30599] Table Description: [u'ip_address', u'character varying(256)', u'zstd', False, 0, False, None]
-- [30599] Table Description: [u'attempt_email', u'character varying(256)', u'zstd', False, 0, False, None]
-- [30599] Table Description: [u'exp_level_0', u'double precision', u'zstd', False, 0, False, None]
-- [30599] Table Description: [u'exp_level_1', u'double precision', u'zstd', False, 0, False, None]
-- [30599] Table Description: [u'exp_level_2', u'double precision', u'zstd', False, 0, False, None]
-- [30599] Table Description: [u'exp_level_3', u'double precision', u'zstd', False, 0, False, None]
-- [30599] Table Description: [u'exp_level_4', u'double precision', u'zstd', False, 0, False, None]
-- [30599] Table Description: [u'exp_level_5', u'double precision', u'zstd', False, 0, False, None]
-- [30599] Table Description: [u'exp_level_6', u'double precision', u'zstd', False, 0, False, None]
-- [30599] Table Description: [u'continent_code', u'character varying(256)', u'zstd', False, 0, False, None]
-- [30599] Table Description: [u'country', u'character varying(256)', u'zstd', False, 0, False, None]
-- [30599] Table Description: [u'state', u'character varying(256)', u'zstd', False, 0, False, None]
-- [30599] Table Description: [u'norm_email', u'character varying(256)', u'zstd', False, 0, False, None]
-- [30599] Table Description: [u'u_name', u'character varying(256)', u'zstd', False, 0, False, None]
-- [30599] Table Description: [u'u_country', u'character varying(256)', u'zstd', False, 0, False, None]
-- [30599] Table Description: [u'u_ranking', u'character varying(256)', u'zstd', False, 0, False, None]
-- [30599] Table Description: [u'product_rating', u'double precision', u'zstd', False, 0, False, None]
-- [30599] Table Description: [u'comments', u'character varying(10000)', u'zstd', False, 0, False, None]
-- [30599] Table Description: [u'question_id', u'character varying(256)', u'zstd', False, 0, False, None]
-- [30599] Table Description: [u'question_weight', u'double precision', u'zstd', False, 0, False, None]
-- [30599] Table Description: [u'score_pct', u'double precision', u'zstd', False, 0, False, None]
-- [30599] Table Description: [u'language_c', u'double precision', u'none', False, 0, False, None]
-- [30599] Table Description: [u'language_cpp', u'double precision', u'none', False, 0, False, None]
-- [30599] Table Description: [u'language_java', u'double precision', u'none', False, 0, False, None]
-- [30599] Table Description: [u'language_javascript', u'double precision', u'none', False, 0, False, None]
-- [30599] Table Description: [u'language_python', u'double precision', u'none', False, 0, False, None]
-- [30599] Table Description: [u'language_sql', u'double precision', u'none', False, 0, False, None]
-- [30599] Table Description: [u'tag_algorithms', u'double precision', u'none', False, 0, False, None]
-- [30599] Table Description: [u'tag_core_cs', u'double precision', u'none', False, 0, False, None]
-- [30599] Table Description: [u'tag_data_structures', u'double precision', u'none', False, 0, False, None]
-- [30599] Table Description: [u'tag_debugging', u'double precision', u'none', False, 0, False, None]
-- [30599] Table Description: [u'tag_math', u'double precision', u'none', False, 0, False, None]
-- [30599] Table Description: [u'tag_optimization', u'double precision', u'none', False, 0, False, None]
-- [30599] Table Description: [u'tag_problem_solving', u'double precision', u'none', False, 0, False, None]
-- [30599] Table Description: [u'tag_programming_basics', u'double precision', u'none', False, 0, False, None]
-- [30599] Table Description: [u'weighted_score', u'double precision', u'zstd', False, 0, False, None]
-- [30599] Table Description: [u'scored_tag_algorithms', u'double precision', u'none', False, 0, False, None]
-- [30599] Table Description: [u'scored_tag_core_cs', u'double precision', u'none', False, 0, False, None]
-- [30599] Table Description: [u'scored_tag_data_structures', u'double precision', u'none', False, 0, False, None]
-- [30599] Table Description: [u'scored_tag_debugging', u'double precision', u'none', False, 0, False, None]
-- [30599] Table Description: [u'scored_tag_math', u'double precision', u'none', False, 0, False, None]
-- [30599] Table Description: [u'scored_tag_optimization', u'double precision', u'none', False, 0, False, None]
-- [30599] Table Description: [u'scored_tag_problem_solving', u'double precision', u'none', False, 0, False, None]
-- [30599] Table Description: [u'scored_tag_programming_basics', u'double precision', u'none', False, 0, False, None]
-- [30599] Table Description: [u'scored_language_c', u'double precision', u'none', False, 0, False, None]
-- [30599] Table Description: [u'scored_language_cpp', u'double precision', u'none', False, 0, False, None]
-- [30599] Table Description: [u'scored_language_java', u'double precision', u'none', False, 0, False, None]
-- [30599] Table Description: [u'scored_language_javascript', u'double precision', u'none', False, 0, False, None]
-- [30599] Table Description: [u'scored_language_python', u'double precision', u'none', False, 0, False, None]
-- [30599] Table Description: [u'scored_language_sql', u'double precision', u'none', False, 0, False, None]
-- [30599] Table Description: [u'norm_question_score', u'double precision', u'zstd', False, 0, False, None]
-- [30599] Table Description: [u'norm_question_score_pct', u'double precision', u'zstd', False, 0, False, None]
-- [30599] Analyzed Compression Row State: [u'rankings', u'attempt_id', u'zstd', u'0.00']
-- [30599] Column attempt_id will be encoded as zstd (previous zstd)
-- [30599] Analyzed Compression Row State: [u'rankings', u'time_taken', u'az64', u'0.00']
-- [30599] Column time_taken will be encoded as az64 (previous az64)
-- [30599] Analyzed Compression Row State: [u'rankings', u'test_id', u'zstd', u'0.00']
-- [30599] Column test_id will be encoded as zstd (previous zstd)
-- [30599] Analyzed Compression Row State: [u'rankings', u'ip_address', u'zstd', u'0.00']
-- [30599] Column ip_address will be encoded as zstd (previous zstd)
-- [30599] Analyzed Compression Row State: [u'rankings', u'attempt_email', u'zstd', u'0.00']
-- [30599] Column attempt_email will be encoded as zstd (previous zstd)
-- [30599] Analyzed Compression Row State: [u'rankings', u'exp_level_0', u'zstd', u'0.00']
-- [30599] Column exp_level_0 will be encoded as zstd (previous zstd)
-- [30599] Analyzed Compression Row State: [u'rankings', u'exp_level_1', u'zstd', u'0.00']
-- [30599] Column exp_level_1 will be encoded as zstd (previous zstd)
-- [30599] Analyzed Compression Row State: [u'rankings', u'exp_level_2', u'zstd', u'0.00']
-- [30599] Column exp_level_2 will be encoded as zstd (previous zstd)
-- [30599] Analyzed Compression Row State: [u'rankings', u'exp_level_3', u'zstd', u'0.00']
-- [30599] Column exp_level_3 will be encoded as zstd (previous zstd)
-- [30599] Analyzed Compression Row State: [u'rankings', u'exp_level_4', u'zstd', u'0.00']
-- [30599] Column exp_level_4 will be encoded as zstd (previous zstd)
-- [30599] Analyzed Compression Row State: [u'rankings', u'exp_level_5', u'zstd', u'0.00']
-- [30599] Column exp_level_5 will be encoded as zstd (previous zstd)
-- [30599] Analyzed Compression Row State: [u'rankings', u'exp_level_6', u'zstd', u'0.00']
-- [30599] Column exp_level_6 will be encoded as zstd (previous zstd)
-- [30599] Analyzed Compression Row State: [u'rankings', u'continent_code', u'zstd', u'0.00']
-- [30599] Column continent_code will be encoded as zstd (previous zstd)
-- [30599] Analyzed Compression Row State: [u'rankings', u'country', u'zstd', u'0.00']
-- [30599] Column country will be encoded as zstd (previous zstd)
-- [30599] Analyzed Compression Row State: [u'rankings', u'state', u'zstd', u'0.00']
-- [30599] Column state will be encoded as zstd (previous zstd)
-- [30599] Analyzed Compression Row State: [u'rankings', u'norm_email', u'zstd', u'0.00']
-- [30599] Column norm_email will be encoded as zstd (previous zstd)
-- [30599] Analyzed Compression Row State: [u'rankings', u'u_name', u'zstd', u'0.00']
-- [30599] Column u_name will be encoded as zstd (previous zstd)
-- [30599] Analyzed Compression Row State: [u'rankings', u'u_country', u'zstd', u'0.00']
-- [30599] Column u_country will be encoded as zstd (previous zstd)
-- [30599] Analyzed Compression Row State: [u'rankings', u'u_ranking', u'zstd', u'0.00']
-- [30599] Column u_ranking will be encoded as zstd (previous zstd)
-- [30599] Analyzed Compression Row State: [u'rankings', u'product_rating', u'zstd', u'0.00']
-- [30599] Column product_rating will be encoded as zstd (previous zstd)
-- [30599] Analyzed Compression Row State: [u'rankings', u'comments', u'zstd', u'0.00']
-- [30599] Column comments will be encoded as zstd (previous zstd)
-- [30599] Analyzed Compression Row State: [u'rankings', u'question_id', u'zstd', u'0.00']
-- [30599] Column question_id will be encoded as zstd (previous zstd)
-- [30599] Analyzed Compression Row State: [u'rankings', u'question_weight', u'zstd', u'0.00']
-- [30599] Column question_weight will be encoded as zstd (previous zstd)
-- [30599] Analyzed Compression Row State: [u'rankings', u'score_pct', u'zstd', u'0.00']
-- [30599] Column score_pct will be encoded as zstd (previous zstd)
-- [30599] Analyzed Compression Row State: [u'rankings', u'language_c', u'raw', u'0.00']
-- [30599] Column language_c will be encoded as raw (previous raw)
-- [30599] Analyzed Compression Row State: [u'rankings', u'language_cpp', u'raw', u'0.00']
-- [30599] Column language_cpp will be encoded as raw (previous raw)
-- [30599] Analyzed Compression Row State: [u'rankings', u'language_java', u'raw', u'0.00']
-- [30599] Column language_java will be encoded as raw (previous raw)
-- [30599] Analyzed Compression Row State: [u'rankings', u'language_javascript', u'raw', u'0.00']
-- [30599] Column language_javascript will be encoded as raw (previous raw)
-- [30599] Analyzed Compression Row State: [u'rankings', u'language_python', u'raw', u'0.00']
-- [30599] Column language_python will be encoded as raw (previous raw)
-- [30599] Analyzed Compression Row State: [u'rankings', u'language_sql', u'raw', u'0.00']
-- [30599] Column language_sql will be encoded as raw (previous raw)
-- [30599] Analyzed Compression Row State: [u'rankings', u'tag_algorithms', u'raw', u'0.00']
-- [30599] Column tag_algorithms will be encoded as raw (previous raw)
-- [30599] Analyzed Compression Row State: [u'rankings', u'tag_core_cs', u'raw', u'0.00']
-- [30599] Column tag_core_cs will be encoded as raw (previous raw)
-- [30599] Analyzed Compression Row State: [u'rankings', u'tag_data_structures', u'raw', u'0.00']
-- [30599] Column tag_data_structures will be encoded as raw (previous raw)
-- [30599] Analyzed Compression Row State: [u'rankings', u'tag_debugging', u'raw', u'0.00']
-- [30599] Column tag_debugging will be encoded as raw (previous raw)
-- [30599] Analyzed Compression Row State: [u'rankings', u'tag_math', u'raw', u'0.00']
-- [30599] Column tag_math will be encoded as raw (previous raw)
-- [30599] Analyzed Compression Row State: [u'rankings', u'tag_optimization', u'raw', u'0.00']
-- [30599] Column tag_optimization will be encoded as raw (previous raw)
-- [30599] Analyzed Compression Row State: [u'rankings', u'tag_problem_solving', u'raw', u'0.00']
-- [30599] Column tag_problem_solving will be encoded as raw (previous raw)
-- [30599] Analyzed Compression Row State: [u'rankings', u'tag_programming_basics', u'raw', u'0.00']
-- [30599] Column tag_programming_basics will be encoded as raw (previous raw)
-- [30599] Analyzed Compression Row State: [u'rankings', u'weighted_score', u'zstd', u'0.00']
-- [30599] Column weighted_score will be encoded as zstd (previous zstd)
-- [30599] Analyzed Compression Row State: [u'rankings', u'scored_tag_algorithms', u'raw', u'0.00']
-- [30599] Column scored_tag_algorithms will be encoded as raw (previous raw)
-- [30599] Analyzed Compression Row State: [u'rankings', u'scored_tag_core_cs', u'raw', u'0.00']
-- [30599] Column scored_tag_core_cs will be encoded as raw (previous raw)
-- [30599] Analyzed Compression Row State: [u'rankings', u'scored_tag_data_structures', u'raw', u'0.00']
-- [30599] Column scored_tag_data_structures will be encoded as raw (previous raw)
-- [30599] Analyzed Compression Row State: [u'rankings', u'scored_tag_debugging', u'raw', u'0.00']
-- [30599] Column scored_tag_debugging will be encoded as raw (previous raw)
-- [30599] Analyzed Compression Row State: [u'rankings', u'scored_tag_math', u'raw', u'0.00']
-- [30599] Column scored_tag_math will be encoded as raw (previous raw)
-- [30599] Analyzed Compression Row State: [u'rankings', u'scored_tag_optimization', u'raw', u'0.00']
-- [30599] Column scored_tag_optimization will be encoded as raw (previous raw)
-- [30599] Analyzed Compression Row State: [u'rankings', u'scored_tag_problem_solving', u'raw', u'0.00']
-- [30599] Column scored_tag_problem_solving will be encoded as raw (previous raw)
-- [30599] Analyzed Compression Row State: [u'rankings', u'scored_tag_programming_basics', u'raw', u'0.00']
-- [30599] Column scored_tag_programming_basics will be encoded as raw (previous raw)
-- [30599] Analyzed Compression Row State: [u'rankings', u'scored_language_c', u'raw', u'0.00']
-- [30599] Column scored_language_c will be encoded as raw (previous raw)
-- [30599] Analyzed Compression Row State: [u'rankings', u'scored_language_cpp', u'raw', u'0.00']
-- [30599] Column scored_language_cpp will be encoded as raw (previous raw)
-- [30599] Analyzed Compression Row State: [u'rankings', u'scored_language_java', u'raw', u'0.00']
-- [30599] Column scored_language_java will be encoded as raw (previous raw)
-- [30599] Analyzed Compression Row State: [u'rankings', u'scored_language_javascript', u'raw', u'0.00']
-- [30599] Column scored_language_javascript will be encoded as raw (previous raw)
-- [30599] Analyzed Compression Row State: [u'rankings', u'scored_language_python', u'raw', u'0.00']
-- [30599] Column scored_language_python will be encoded as raw (previous raw)
-- [30599] Analyzed Compression Row State: [u'rankings', u'scored_language_sql', u'raw', u'0.00']
-- [30599] Column scored_language_sql will be encoded as raw (previous raw)
-- [30599] Analyzed Compression Row State: [u'rankings', u'norm_question_score', u'zstd', u'0.00']
-- [30599] Column norm_question_score will be encoded as zstd (previous zstd)
-- [30599] Analyzed Compression Row State: [u'rankings', u'norm_question_score_pct', u'zstd', u'0.00']
-- [30599] Column norm_question_score_pct will be encoded as zstd (previous zstd)
-- [30599] Column Encoding resulted in an identical table - no changes will be made
-- [30599] Performed modification of 0 tables
-- [30599] Processing Complete
@IanMeyers

This comment has been minimized.

Copy link

IanMeyers commented Feb 7, 2020

Can you paste the output of the analyze compression command please? How many rows/how large is the table?

@IanMeyers

This comment has been minimized.

Copy link

IanMeyers commented Feb 7, 2020

Can you also please paste the table DDL so I can see the column data types?

@patilarpith

This comment has been minimized.

Copy link
Owner Author

patilarpith commented Feb 8, 2020

Analyze Compression:

Column Encoding Est_reduction_pct
attempt_id zstd 0
time_taken az64 0
test_id zstd 0
ip_address zstd 0
attempt_email zstd 0
exp_level_0 zstd 0
exp_level_1 zstd 0
exp_level_2 zstd 0
exp_level_3 zstd 0
exp_level_4 zstd 0
exp_level_5 zstd 0
exp_level_6 zstd 0
continent_code zstd 0
country zstd 0
state zstd 0
norm_email zstd 0
u_name zstd 0
u_country zstd 0
u_ranking zstd 0
product_rating zstd 0
comments zstd 0
question_id zstd 0
question_weight zstd 0
score_pct zstd 0
language_c raw 0
language_cpp raw 0
language_java raw 0
language_javascript raw 0
language_python raw 0
language_sql raw 0
tag_algorithms raw 0
tag_core_cs raw 0
tag_data_structures raw 0
tag_debugging raw 0
tag_math raw 0
tag_optimization raw 0
tag_problem_solving raw 0
tag_programming_basics raw 0
weighted_score zstd 0
scored_tag_algorithms raw 0
scored_tag_core_cs raw 0
scored_tag_data_structures raw 0
scored_tag_debugging raw 0
scored_tag_math raw 0
scored_tag_optimization raw 0
scored_tag_problem_solving raw 0
scored_tag_programming_basics raw 0
scored_language_c raw 0
scored_language_cpp raw 0
scored_language_java raw 0
scored_language_javascript raw 0
scored_language_python raw 0
scored_language_sql raw 0
norm_question_score zstd 0
norm_question_score_pct zstd 0
@patilarpith

This comment has been minimized.

Copy link
Owner Author

patilarpith commented Feb 8, 2020

Table DDL:

column type encoding distkey sortkey notnull
attempt_id character varying(256) zstd false 0 false
time_taken timestamp without time zone az64 false 0 false
test_id character varying(256) zstd false 0 false
ip_address character varying(256) zstd false 0 false
attempt_email character varying(256) zstd false 0 false
exp_level_0 double precision zstd false 0 false
exp_level_1 double precision zstd false 0 false
exp_level_2 double precision zstd false 0 false
exp_level_3 double precision zstd false 0 false
exp_level_4 double precision zstd false 0 false
exp_level_5 double precision zstd false 0 false
exp_level_6 double precision zstd false 0 false
continent_code character varying(256) zstd false 0 false
country character varying(256) zstd false 0 false
state character varying(256) zstd false 0 false
norm_email character varying(256) zstd false 0 false
u_name character varying(256) zstd false 0 false
u_country character varying(256) zstd false 0 false
u_ranking character varying(256) zstd false 0 false
product_rating double precision zstd false 0 false
comments character varying(10000) zstd false 0 false
question_id character varying(256) zstd false 0 false
question_weight double precision zstd false 0 false
score_pct double precision zstd false 0 false
language_c double precision none false 0 false
language_cpp double precision none false 0 false
language_java double precision none false 0 false
language_javascript double precision none false 0 false
language_python double precision none false 0 false
language_sql double precision none false 0 false
tag_algorithms double precision none false 0 false
tag_core_cs double precision none false 0 false
tag_data_structures double precision none false 0 false
tag_debugging double precision none false 0 false
tag_math double precision none false 0 false
tag_optimization double precision none false 0 false
tag_problem_solving double precision none false 0 false
tag_programming_basics double precision none false 0 false
weighted_score double precision zstd false 0 false
scored_tag_algorithms double precision none false 0 false
scored_tag_core_cs double precision none false 0 false
scored_tag_data_structures double precision none false 0 false
scored_tag_debugging double precision none false 0 false
scored_tag_math double precision none false 0 false
scored_tag_optimization double precision none false 0 false
scored_tag_problem_solving double precision none false 0 false
scored_tag_programming_basics double precision none false 0 false
scored_language_c double precision none false 0 false
scored_language_cpp double precision none false 0 false
scored_language_java double precision none false 0 false
scored_language_javascript double precision none false 0 false
scored_language_python double precision none false 0 false
scored_language_sql double precision none false 0 false
norm_question_score double precision zstd false 0 false
norm_question_score_pct double precision zstd false 0 false
@patilarpith

This comment has been minimized.

Copy link
Owner Author

patilarpith commented Feb 8, 2020

The total size of the table is 6380mb and uncompressed size is 3080mb

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.