我注意到，AWS Redshift建议使用与它在将数据（通过COPY）加载到空表时自动创建的列压缩编码不同的列压缩编码。

例如，我创建了一个表并从S3加载数据，如下所示：

CREATE TABLE Client (Id varchar(511) , ClientId integer , CreatedOn timestamp, 
UpdatedOn timestamp ,  DeletedOn timestamp , LockVersion integer , RegionId 
varchar(511) , OfficeId varchar(511) , CountryId varchar(511) ,  
FirstContactDate timestamp , DidExistPre boolean , IsActive boolean , 
StatusReason integer ,  CreatedById varchar(511) , IsLocked boolean , 
LockType integer , KeyWorker varchar(511) ,  InactiveDate timestamp , 
Current_Flag varchar(511) );

Table Client创建的执行时间：0.3秒

copy Client from 's3://<bucket-name>/<folder>/Client.csv' 
credentials 'aws_access_key_id=<access key>; aws_secret_access_key=<secret>' 
csv fillrecord truncatecolumns ignoreheader 1 timeformat as 'YYYY-MM-
DDTHH:MI:SS' gzip acceptinvchars compupdate on region 'ap-southeast-2';

警告：已成功加载到表“ client”中，成功加载了24284条记录。完成加载到表“
client”中，已加载6条记录，并替换了ACCEPTINVCHARS。检查“ stl_replacements”系统表以了解详细信息。

0行受影响的COPY成功执行

执行时间：3.39s

完成此操作后，我可以查看COPY已应用的列压缩编码：

select "column", type, encoding, distkey, sortkey, "notnull" 
from pg_table_def where tablename = 'client';

给予：

╔══════════════════╦═════════════════════════════╦═══════╦═══════╦═══╦═══════╗
║ id               ║ character varying(511)      ║ lzo   ║ false ║ 0 ║ false ║
║ clientid         ║ integer                     ║ delta ║ false ║ 0 ║ false ║
║ createdon        ║ timestamp without time zone ║ lzo   ║ false ║ 0 ║ false ║
║ updatedon        ║ timestamp without time zone ║ lzo   ║ false ║ 0 ║ false ║
║ deletedon        ║ timestamp without time zone ║ none  ║ false ║ 0 ║ false ║
║ lockversion      ║ integer                     ║ delta ║ false ║ 0 ║ false ║
║ regionid         ║ character varying(511)      ║ lzo   ║ false ║ 0 ║ false ║
║ officeid         ║ character varying(511)      ║ lzo   ║ false ║ 0 ║ false ║
║ countryid        ║ character varying(511)      ║ lzo   ║ false ║ 0 ║ false ║
║ firstcontactdate ║ timestamp without time zone ║ lzo   ║ false ║ 0 ║ false ║
║ didexistprecirts ║ boolean                     ║ none  ║ false ║ 0 ║ false ║
║ isactive         ║ boolean                     ║ none  ║ false ║ 0 ║ false ║
║ statusreason     ║ integer                     ║ none  ║ false ║ 0 ║ false ║
║ createdbyid      ║ character varying(511)      ║ lzo   ║ false ║ 0 ║ false ║
║ islocked         ║ boolean                     ║ none  ║ false ║ 0 ║ false ║
║ locktype         ║ integer                     ║ lzo   ║ false ║ 0 ║ false ║
║ keyworker        ║ character varying(511)      ║ lzo   ║ false ║ 0 ║ false ║
║ inactivedate     ║ timestamp without time zone ║ lzo   ║ false ║ 0 ║ false ║
║ current_flag     ║ character varying(511)      ║ lzo   ║ false ║ 0 ║ false ║
╚══════════════════╩═════════════════════════════╩═══════╩═══════╩═══╩═══════╝

然后，我可以这样做：

analyze compression client;

给予：

鈺斺晲鈺愨晲鈺愨晲鈺愨晲鈺愨暒鈺愨晲鈺愨晲鈺愨晲鈺愨晲鈺愨晲鈺愨晲鈺愨晲鈺愨晲鈺愨晲鈺︹晲鈺愨晲鈺愨晲鈺愨晲鈺︹晲鈺愨晲鈺愨晲鈺愨晲鈺�
鈺� client 鈺� id               鈺� zstd  鈺� 40.59 鈺�
鈺� client 鈺� clientid         鈺� delta 鈺� 0.00  鈺�
鈺� client 鈺� createdon        鈺� zstd  鈺� 19.85 鈺�
鈺� client 鈺� updatedon        鈺� zstd  鈺� 12.59 鈺�
鈺� client 鈺� deletedon        鈺� raw   鈺� 0.00  鈺�
鈺� client 鈺� lockversion      鈺� zstd  鈺� 39.12 鈺�
鈺� client 鈺� regionid         鈺� zstd  鈺� 54.47 鈺�
鈺� client 鈺� officeid         鈺� zstd  鈺� 88.84 鈺�
鈺� client 鈺� countryid        鈺� zstd  鈺� 79.13 鈺�
鈺� client 鈺� firstcontactdate 鈺� zstd  鈺� 22.31 鈺�
鈺� client 鈺� didexistprecirts 鈺� raw   鈺� 0.00  鈺�
鈺� client 鈺� isactive         鈺� raw   鈺� 0.00  鈺�
鈺� client 鈺� statusreason     鈺� raw   鈺� 0.00  鈺�
鈺� client 鈺� createdbyid      鈺� zstd  鈺� 52.43 鈺�
鈺� client 鈺� islocked         鈺� raw   鈺� 0.00  鈺�
鈺� client 鈺� locktype         鈺� zstd  鈺� 63.01 鈺�
鈺� client 鈺� keyworker        鈺� zstd  鈺� 38.79 鈺�
鈺� client 鈺� inactivedate     鈺� zstd  鈺� 25.40 鈺�
鈺� client 鈺� current_flag     鈺� zstd  鈺� 90.51 鈺�
鈺氣晲鈺愨晲鈺愨晲鈺愨晲鈺愨暕鈺愨晲鈺愨晲鈺愨晲鈺愨晲鈺愨晲鈺愨晲鈺愨晲鈺愨晲鈺愨晲鈺┾晲鈺愨晲鈺愨晲鈺愨晲鈺┾晲鈺愨晲鈺愨晲鈺愨晲鈺�

即完全不同的结果。

我很想知道为什么会这样？我发现〜24K记录少于AWS指定的有意义的压缩分析样本所需的100K记录，但是对于相同的24K行表，COPY和ANALYZE给出不同的结果仍然感到奇怪。