-
Notifications
You must be signed in to change notification settings - Fork 11
Indexing Postgres Tables
Matthew Perry edited this page Jul 22, 2013
·
9 revisions
Beyond what django does automatically, we create these indexes:
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);
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;