What are effective ways of writing faster queries specifically in Postgres? Please do not include generally good database practices (eg use indexes or normalization). I'm looking for hints like derived tables work faster than subqueries or using python string functions seems faster than the pgsql string functions. Ideally, this list would include real-world examples and advice.
Thanks.
Make sure to vacuum periodically. The current autovacuum system does a pretty good job of this in most cases, but it can still be helpful to run a full manual vacuum periodically (in our case this happens approximately once per year).
Indexes are only used reliably if stats are available for the table. Make sure that a vacuum --analyze is run after any major changes to a table (tons of inserts/deletes) to insure that indexes are selected properly.
The default Postgres configuration is optimized for a system with relatively modest resources and slow disks. If your system has faster disks (possibly), a faster CPU (probably), or a lot more RAM (almost certainly), then make sure to tune the various parameters based upon that. The main thing is to increase buffer sizes, but if you have extremely fast disks (especially SSDs), it would be a good idea to lower the cost estimates for seek times as well.
I've also had a few experiences with slightly slow joins in fairly complex queries, but these are much more difficult to generalize. Just generally it helps to be more explicit with the query than what may be required in a db with a more sophisticated query optimizer (eg, Oracle or DB2).
The only unbreakable rule I have found so far is that there are no unbreakable rules.
Sometimes subquery is faster, sometimes join is faster. Sometimes you want plpgsql, sometimes you want some other pl/*.
The most usually adequate advises: