SuffolkLITLab/EfileProxyServer

Create indices for high-row count tables

Closed this issue · 0 comments

I'd noticed recently that the Efile proxy server was being a bit slow. I had always assumed that it was our calls to Tyler's service that were taking a long time, but after taking a closer look at log timestamps, it was in fact our own code that was taking a long time. Eventually narrowed things down to a few SQL tables that had a lot of rows. For silly reasons, I had assumed that postegres would automatically 1) make indices on your tables and 2) vacuum the tables automatically; both of those assumptions were incorrect, and certain queries (particularly to the optionalservices, filing, and filingcomponent tables) were taking multiple seconds. Adding just one index over the court location, something we use to filter almost every SQL query, increases the speed ~500x.

Creating the indices manually is simple and already has been done, but we need to add the ability to automatically create these indices if they aren't there already, and to vacuum those tables on the nightly updates.