ANALYZE command in Redshift fails

awsredshiftvacuum

I created a table in AWS Redshift (4-node dc1 cluster) in the following manner.

CREATE TABLE events
(
    event_id int,
    event_date int ,
    event_month_year int ,
    event_year int ,
    event_fraction_date numeric(10,4) ,
    event_actor1_code varchar(100) ,
    event_actor1_name varchar(100) ,
    event_actor1_country_code varchar(100) ,
    event_actor1_known_group_code varchar(100) ,
    event_actor1_ethnic_code varchar(100) ,
    event_actor1_religion1_code varchar(100) ,
    event_actor1_religion2_code varchar(100) ,
    event_actor1_type1_code varchar(100) ,
    event_actor1_type2_code varchar(100) ,
    event_actor1_type3_code varchar(100) ,
    event_actor2_code varchar(100) ,
    event_actor2_name varchar(100) ,
    event_actor2_country_code varchar(100) ,
    event_actor2_known_group_code varchar(100) ,
    event_actor2_ethnic_code varchar(100) ,
    event_actor2_religion1_code varchar(100) ,
    event_actor2_religion2_code varchar(100) ,
    event_actor2_type1_code varchar(100) ,
    event_actor2_type2_code varchar(100) ,
    event_actor2_type3_code varchar(100),   
    is_root_event varchar(100) ,
    event_code varchar(100) ,
    event_base_code varchar(100) ,
    event_root_code varchar(100) ,
    event_quad_class int ,
    event_goldstein_scale float ,
    event_num_mentions int ,
    event_num_sources int ,
    event_num_articles int ,
    event_avg_tone float ,
    event_actor1_geo_type int ,
    event_Actor1_Geo_Full_Name varchar(500) encode lzo,
    event_Actor1_Geo_Country_Code varchar(100) ,
    event_Actor1_Geo_ADM1_Code varchar(100) ,
    event_Actor1_Geo_Lat float ,
    event_Actor1_Geo_Long float ,
    event_Actor1_Geo_FeatureID varchar(100) ,
    event_Actor2_Geo_Type int ,
    event_Actor2_Geo_Full_Name varchar(500) encode lzo,
    event_Actor2_Geo_Country_Code varchar(100) ,
    event_Actor2_Geo_ADM1_Code varchar(100) ,
    event_Actor2_Geo_Lat float ,
    event_Actor2_Geo_Long float ,
    event_Actor2_Geo_FeatureID varchar(100) ,
    event_Action_Geo_Type int ,
    event_Action_Geo_Full_Name varchar(500) encode lzo,
    event_Action_Geo_Country_Code varchar(100) ,    
    event_Action_Geo_ADM1_Code varchar(100) ,
    event_Action_Geo_Lat float ,
    event_Action_Geo_Long float ,
    event_Action_Geo_FeatureID varchar(100) ,
    event_date_added int ,
    event_source_url varchar(500) encode lzo
)
diststyle key
distkey(event_id);

I performed a COPY of around 60k records.

Post that, I performed a VACUUM and ANALYZE. It fails at the ANALYZE command with the following error:

[Amazon](500310) Invalid operation: index "pg_toast_16408_index" is not a btree;

I tried dropping and re-creating. But I get the same error. But once I change the diststyle to ALL, then the error disappears.

Any help on this would be very much appreciated.

Best Answer

A restore of the snapshot database resolved the error. Looks like the index was corrupted.