Index needed on jobexecutionid column in step_execution table
TweetPosted on Monday Jan 05, 2015 at 09:56PM in jberetweb
Today I deployed new jberetweb to my production system which have 1 million rows in step_execution table, and felt jberetweb slow. I looked it and found the cause is sequential scan.
jbatch=# explain analyze select * from step_execution where jobexecutionid = 384846;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------
Seq Scan on step_execution (cost=0.00..36817.53 rows=4 width=626) (actual time=101.046..101.047 rows=2 loops=1)
Filter: (jobexecutionid = 384846)
Rows Removed by Filter: 1102459
Total runtime: 101.074 ms
So I created an index on jobexecutionid column in step_execution table, then jberetweb starts running fast.
jbatch=# create index step_execution_jobexecutionid_idx on step_execution (jobexecutionid);
CREATE INDEX
jbatch=# explain analyze select * from step_execution where jobexecutionid = 384846;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------
Index Scan using step_execution_jobexecutionid_idx on step_execution (cost=0.43..8.50 rows=4 width=626) (actual time=0.054..0.056 rows=2 loops=1)
Index Cond: (jobexecutionid = 384846)
Total runtime: 0.113 ms
Maybe more indexes are needed for default JBeret schema if I implement some filtering function to jberetweb.
