On Mon, Oct 8, 2012 at 1:27 PM, Craig Ringer
<ringerc@ringerc.id.au> wrote:
If you already have appropriate indexes and have used `explain analyze` to verify that the query isn't doing anything slow and expensive, it's possible the easiest way to improve performance is to set up async replication or log shipping to a local hot standby on real physical hardware, then do the query there.
I've run postgresql on medium instances using elastic block store for the storage and had no difficulty running queries like this one on tables of comparable (and larger) size. It might not come back in 10ms, but such queries weren't so slow that I would describe the wait as "a lot of time" either. My guess is that this is a sequential scan on a 10 million record table with lots of bloat due to updates. Without more info about table structure and explain analyze output, we are all just guessing, though. Please read the wiki page which describes how to submit performance problems and restate your question.