As mentioned by sargun (who was irritatingly downvoted by someone) the difference is that PostgreSQL uses the MVCC concurrency model, which allows you to do quite complex queries concurrently with other people making writes to the database, but comes at the cost that there is no longer any objective count of the rows in a given table: the number of rows is related to which transactions are considered live at any given moment.

http://wiki.postgresql.org/wiki/Slow_Counting

In my case, this was inadvertently a good comparison. Most of the "ad-hoc" queries we're doing are AVG, SUM, or similar operations that require scanning the entire table.

I may try to edit this post to compare those operations, as it may be more meaningful.

Thanks for posting the link. TIL.

I thought this query was instant in most dbs because it reads an internal row count of the table since this particular query has no conditions or joins.

Does H2 do MVCC?

AFAIK not by default. Furthermore the MVCC mode is still considered experimental: http://www.h2database.com/html/advanced.html#mvcc