takes 10 minutes on PostgreSQL to return the result 21454134?
With H2, an open source embedded SQL database I use daily, a "select count( * ) from table_name" query returns the result instantly. I assumed therefore that this was the norm...
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.
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.
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.
Comments
select count( * ) from dummy_table;
takes 10 minutes on PostgreSQL to return the result 21454134?
With H2, an open source embedded SQL database I use daily, a "select count( * ) from table_name" query returns the result instantly. I assumed therefore that this was the norm...
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