Paul Ramsey from Crunchy Data brings us 'PostGIS Performance Tips'.

The most common cause of performance issues in PostGIS is data irregularity. Fortunately there are straightforward ways to address funky data to get cleaner faster queries. There are also a bunch of techniques from the nonspatial world, such as data presummarizing, partitioning and denormalizing that can be used to turbo charge your systems through slightly altering your data model and query assumptions. Even extremely large databases can be made high performance, if you are able to constrain your assumptions about the potential variability of query parameters, just a little.