Sanity checking big select performance

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.