rankings.sql

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

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

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

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

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

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