[TASK] Confirm tables and geometry has index and/or spatial index.

[LorneLeonard-NOAA]

Need to confirm tables have index and/or Spatial Index keys

• Create Spreadsheet with missing index list.
• Create Spreadsheet with missing primary key list.
• Identify any tables that do not need a index.
• Identify DB/table code with index Flags i.e. create_table_df.to_sql( index=False/True
• Identify DB/table code without index Flags
• Identify DB/table code missing index Check i.e. within sql code
• Develop scripts that:
  • Scans tables and confirms index is enabled,
  • Log index changes
• Confirm DB settings
  • Log index changes
  • Settings to enforce index
• Change the Data load system and propagation of data systems (TI to UAT to Prod)
• Identify and apply code to include ANALYZE keyword to improve performance.

[LorneLeonard-NOAA]

select * from pg_tables where not hasindexes

1362 rows missing index. Note: Not all of these require a index.
217 rows have index.

• 93 tables in ingest schema missing index
• 329 tables in publish schema missing index
• 687 tables in archive schema missing index
• 1 table in tiger schema missing index
• 4 tables in information_schema schema missing index
• 51 tables in dev schema missing index
• 136 tables in fim_ingest schema missing index
• 19 tables in derived schema missing index
• 1 table in admin schema missing index
• 7 tables in rnr schema missing index
• 34 tables in cache schema missing index

[LorneLeonard-NOAA]

192 tables missing primary key

[RobHanna-NOAA]

This will also trigger some changes to the hand / ripple / etc data loads system. Most databases are copied raw with schema's from TI to UAT to Prod. This will be a deceptively large task.