Paul Ramsey wants to make spatial querying faster:
A reasonable question to ask, if you are managing a system with variable performance is: “what queries on my system are running slowly?”
Fortunately, PostgreSQL includes an extension called “pg_stat_statements” that tracks query performance over time and maintains a list of high cost queries.
This particular post is more about pg_stat_statements in general rather than specific advice for PostGIS, but it does lay out some recommendations for using pg_stat_statements in spatial-heavy environments.