Indexing Postgres Tables

Beyond what django does automatically, we create these indexes:

Nearest neighbor queries

These seem to make the candidate lookups faster

CREATE INDEX idx_trees_conditionvariantlookup_variant_code ON trees_conditionvariantlookup (variant_code);
CREATE INDEX idx_treelive_summary_test ON treelive_summary (fia_forest_type_name, pct_of_totalba, calc_dbh_class);

But these don't possibly due to the NOT IN clause on fia_forest_type_name?

CREATE INDEX idx_treelive_summary_test ON treelive_summary (fia_forest_type_name, pct_of_totalba, calc_dbh_class);
CREATE INDEX idx_treelive_summary_fia_forest_type_name ON treelive_summary (fia_forest_type_name);
CREATE INDEX idx_treelive_summary_pct_of_totalba ON treelive_summary (pct_of_totalba);

Metrics queries

For where clauses

        CREATE INDEX idx_trees_fvsaggregate_var ON trees_fvsaggregate (var);
        CREATE INDEX idx_trees_fvsaggregate_year ON trees_fvsaggregate (year);
        CREATE INDEX idx_trees_fvsaggregate_cond ON trees_fvsaggregate (cond);
        CREATE INDEX idx_trees_fvsaggregate_rx ON trees_fvsaggregate (rx);
        CREATE INDEX idx_trees_fvsaggregate_offset ON trees_fvsaggregate ("offset");

        CREATE INDEX idx_trees_scenariostand_cond_id ON trees_scenariostand (cond_id);
        CREATE INDEX idx_trees_scenariostand_scenario_id ON trees_scenariostand (scenario_id);
        CREATE INDEX idx_trees_scenariostand_rx_internal_num ON trees_scenariostand (rx_internal_num);
        CREATE INDEX idx_trees_scenariostand_offset ON trees_scenariostand ("offset");

VACUUM ANALYZE;

When loading the data, it can help to remove them

DROP INDEX idx_trees_fvsaggregate_var;
DROP INDEX idx_trees_fvsaggregate_year;
DROP INDEX idx_trees_fvsaggregate_cond;
DROP INDEX idx_trees_fvsaggregate_rx ;
DROP INDEX idx_trees_fvsaggregate_offset;