$work uses OpenNMS to monitor our various devices (servers, switches, routers, printers etc), mostly via SNMP. Today, while looking at the various events that had been recorded, I noticed that a relatively simple search was taking more than 2 minutes to process through ~250,000 event rows (plus associated rows in other tables). I turned on query logging (log_statements = ‘all’ && service postgresql reload), and re-ran the search from the web interface.
Lo and behold, the culprit was revealed – the search went something like
UPPER(eventlogmsg) LIKE ‘%value%’
Even if that field was indexed,the use of the ‘%’ on the front would negate the use of the index. I threw the query into PGAdmin, and discovered that the query plan that PostgreSQL chose was a pair of nested loop joins – unpleasant to say the least. A quick gander at the docs, and a few SQL statements later, and I had a full-text index on the eventlogmsg field. Several test queries convinced me that it was much faster, so I threw the new query into PGAdmin and asked for the pretty query plan. Two hash joins and a sort, and a query time of 31 milliseconds; or more than 5000 times faster.
So, I’ve filed a Bugzilla entry for this with the OpenNMS team – unfortunately it’s specific to PostgreSQL 8.3, but that’s something that can probably be determined at run-time and install-time. Hopefully they agree that it’s a worthwhile performance change, as full-text indexes won’t actually help the LIKE usage – the code will have to change to generate new SQL statements.
