Large tables behave differently
On my bugs duty I’ve noticed that a query was timing out.
The ruby code was simple:
The query that produces is:
Somehow that query is taking a very long time. It’s very strange, we have indices for author_type, author_id and created_at and there aren’t very many messages per user. So why is it slow!?
I tried to run this query instead:
I have only changed the limit from 1 to 10 and it’s way faster!
Somehow Postgres decides to scan the created_at index until it finds the first message that match the conditions.
Once I’ve noticed the oddity I’ve managed to find a stackoverflow question related to the same problem. Read that for a more in-depth explanation: http://stackoverflow.com/questions/21385555/postgresql-query-very-slow-with-limit-1
Due to the increased number of users, the data in our DB have been growing exponentially. It’s a nice problem to have, but several queries that were working fine with smaller amount of rows are now struggling and require our attention.
I think it’s time for me to read a couple of books on Postgres performance.
Interested in making an Impact? Join the carwow-team!
Feeling social? Connect with us on Twitter and LinkedIn :-)