Some helpful database indexes

[Jenny Gurney]

The CNDA was running into some bad database performance issues yesterday.  We recently installed the New Relic agent, which helped us track down the bottle neck.  Our xdat_user_login table had grown to 2.5 million rows. 

This query appeared again and again in the slow query table: 

SELECT session_id, ip_address FROM xdat_user_login WHERE session_id IN (?,?)

By adding an index for the session_id column, we seem to have fixed the issue. 
CREATE INDEX xdat_user_login_session_id_btree
  ON xdat_user_login
  USING btree
  (session_id);



We went on to add a few more performance tweaks, which seem to be helping queries to our workflows table (.5 million rows) as well.  Here are the other indexes we added yesterday:

CREATE INDEX xdat_user_login_login_date_btree
  ON xdat_user_login
  USING btree
  (login_date );
CREATE INDEX wrk_workflowdata_current_step_launch_time_btree
  ON wrk_workflowdata
  USING btree
  (current_step_launch_time );

CREATE INDEX wrk_workflowdata_status_btree
  ON wrk_workflowdata
  USING btree
  (status );

CREATE INDEX wrk_workflowdata_id_btree
  ON wrk_workflowdata
  USING btree
  (id );


Jenny Gurney
CNDA Operations
NRG, Wash U, St. Louis, MO

[steve damon]

Thanks for posting this. We're seeing ~2.6 million rows in our wrk_workflowdata table. 

Best,

Steve

[Cory Johnson]

Do you know if deleting past history in xdat_user_login will cause issues?