Shut up and just give me the code? Ok, look at the bottom of this post.
What is the slowest query on road.lk? It's the select count(*) on the traffic alerts table that Django admin insists on executing each time someone visit the admin page for the alerts. Though we are fully operational only in Sri Lanka and Bangladesh at the moment, we do have twitter stream parsers running for all countries since a few months back. As a result, at the time of writing this blog post the traffic alerts table contains 762,779 entries. That's a number that Postgres can handle easily without breaking a sweat; as long as you don't do a select count(*) on it. In fact Postgres is not the only database that has trouble with these unqualified count queries. Mysql does even worse.
This cannot exactly be described as a bug but it certainly is an issue that has been plaguing django for five years. On the bug tracker it's perhaps rightly categorized as 'wont fix', because to fix it would be to break the paginator which is used in most places. But this can be solved easily by caching the page nav tabs as a template fragment right? Wrong! If you overide the default change_list template, you will see that the paging section does appear to be cached, yet surpsingly, the debug tool bar and the pgsql slow query log reveals that the query is still being executed.
Looks like creating a custom paginator is the only way forward. In the end it turned out to be very easy. All that's needed is to grab the code for the default paginator and change just a few lines of code.