Sanity checking big select performance

Hi friends!

I'd love to get a sanity check on whether a fat select query I'm doing
makes sense given the infrastructure that we have.

We have 3 big tables that we typically join together for certain queries: a
~40 million row photos table, a ~20 million row users table, and a ~50
million row photo_to_album table that maps photos to albums.

We like to display real time analytics, which often results in a query like:

select (random aggregations )
from
photo_to_album join photos on photos.id = photo_to_album.photo_id
join users on users.id = photos.user_id
where
photo_to_album.album_id = <something>
and
photos.created_at between <some dates>
and <other junk>

We have indexes on all of the joins, and the where clauses.

One of these queries that should be targeting something like 300K photos
takes 38 seconds to run (with an aggregate/nested loop taking effectively
all of that time), and then upon second execution with a warm cache, 4
seconds.

Also worryingly, it spikes read IOPS to almost 1500/sec during the time and
write IOPS 200/sec. When not running the query, steady level read iops
basically nil, write hovers around 50-100.

This also increases the queue depth from basically 0 up to 6. Keeping the
queue depth high seems to cause timeouts in other queries. The CPU is
barely if at all affected, hovering around 20%. Memory also barely
affected.

We have a RDS Postgres database, m3.2xlarge with 2000 Provisioned IOPS and
400GB storage. This translates to 8 virtual CPUs, 30GiB memory, and all
SSD drives.

Several questions here:

1) Is that level of IOPS normal?
2) Is it bad that the level of iops can queue requests that screw up the
whole database even if it's just select queries? Especially when the CPU
and Memory are still plentiful?
3) What is up with the huge difference between cold and warm cache?

Any help is appreciated!

- jzc