I spent a good chunk of today trying to chase down why a query on
one box ran in 110ms and on another, smaller box it ran in 10ms.
There was no other activity on either box.
Both boxes are PG9.1.1 RHEL 6.2 x64. the faster box is a smallish
VM. the other box is a big 40core/256GB box.
The plans between both boxes were exactly the same, so it didn't occur
to me to run an analyze on the tables.
I did a number of things including bouncing the DB and reindexing
some of the tables. that didn't help.
Eventually i separated the query out to a prepared statement and
found that it was spending 100ms in PREPARE on the slow box (I
assume it was planning)
I left the problem for about 30 minutes and came back and the
query started running at normal speed.
I suspect an autovacuum kicked in, but would that sort of thing really impact
parse/plan time to that degree? any other thoughts as to what it could have been?