Kohei Nozaki's blog 

Index needed on jobexecutionid column in step_execution table


Posted 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.



No one has commented yet.

Leave a Comment

HTML Syntax: NOT allowed