916279 - Add indexes to raw_crashes to support cron_submitter.sh query

Status: RESOLVED FIXED

(Socorro :: Database, task)

Description

[Selena Deckelmann :selenamarie :selena]

I added a sweet new index to raw_crashes to support a post-rabbitmq deployment query for shipping crashes between prod->stage->dev.  This ticket documents that change, which is just in the database, rather than in any code.

Comment 1

[Selena Deckelmann :selenamarie :selena]

Code to add the indexes to the existing tables was:

 DO $$ DECLARE tablename record;

    BEGIN
    for tablename in SELECT relname from pg_class where relkind = 'r'
        and relname ~ 'raw_crashes_'                                             
    LOOP
        RAISE NOTICE 'creating %', tablename;
        EXECUTE 'CREATE INDEX ' || quote_ident(tablename.relname) || '_date_processed' ||
                ' ON ' || quote_ident(tablename.relname) || ' (date_processed)';   

    END LOOP;                                                                   
  END$$;

Change to the report_partitions_info table was: 

 update report_partition_info set indexes = '{"date_processed"}' where table_name = 'raw_crashes';