pgsql slow query on table of 500 million rows
For postgres, unlike other RDBMS, SELECT COUNT(*) ... from an unindexed table means a full scan of the table. So the first run is purely IO bound. If you want this query - consider adding some indexes to the table. (Without knowing the query I can not tell, what indexes you may need).

Another option - configure postgres to better utilize the RAM you have.