xxxnx - Postgresql updating millions of rows


A single index can wreak tremendous havoc with Postgre SQL's query planner, which we’ve found sometimes operates unpredictably.

To avoid this pitfall, do your own analysis to make sure that your indexes match your usage patterns, and be sure to automatically monitor slow queries, which might crop up at any moment.

(It's slightly more complicated than that, because Rap Genius also has a notion of “base annotations”, so in addition to checking the 9 annotation IDs, we need to check any annotations that have one of those 9 as a base annotation.) The annotations table has millions of records, and many indexes, including single btree indexes on each of .

Queries like the one above were very fast for a while, but then one day, for no apparent reason, they became excruciatingly slow.

We love using Postgres at Tilt, and it makes our lives easier every day.