gothfox/Tiny-Tiny-IRC — pull request #3 by mas90 (2014-02-03 13:01:55+00:00)
Title: new schema: add index to optimise incoming message check

On a fairly busy setup with many thousand messages in the database, I was finding that the check for incoming messages (NativeConnectionHandler.checkMessages(), run every 250ms) was causing PostgreSQL to use a lot of CPU time. The query for incoming messages required a sequential scan of the ttirc_messages table.

Adding an index on the 'incoming' column of this table makes this query much faster, since most of the messages will have incoming=true and PostgreSQL can immediately ignore those.

I've created a new schema version (10) with this index and tested that schema updates work as expected.

Many thanks for an extremely useful app!