Optimising postgres queries is not a good time. I've found CTE (Common Table Expressions) useful in join heavy queries. Also, the right compound indexes can also include key data fields for that space-speed tradeoff. I haven't tried materialised views yet but they are apparently viable for write-infrequent-read-often loads.