On Mon, 14 Apr 2008 13:34:36 -0400
Chris Browne <cbbrowne@acm.org> wrote:
> > Another test. In postgres I added an index to the userid column
> > and then counted distinct userids. The average run time over
> > three queries was 4666 seconds, or 78 minutes. Unbelievable.
> > On SQL Server, with *no* index, the same query takes on average
> > 414 seconds, or about 7 minutes. Ten times faster!
> Some database systems are able to do some optimization where either:
> a) They collect some statistics to answer such queries in O(1)
> time, or b) They can use index accesses and only look at an index.
> The MVCC strategy in PostgreSQL, which allows it to avoid the need
> for readers to block writers, and vice-versa, has the result that
> running "count(*)" without any WHERE clause requires a scan across
> the entire table.
> If the entire purpose of your application is to run COUNT(*) against
> the entireties of a table with 180M rows, then PostgreSQL may not be
> the right database for your application.
> If, on the other hand, this is a poor benchmark of your actual
> requirements, then it would be a bad idea to draw any conclusions
> based on the performance of "select count(*) from some_table;"
But why once you add the index and count distinct the performances
are still so far?
I'd say that counting in this case is not the hardest thing to do,
but rather the "distinct" part.
--
Ivan Sergio Borgonovo
http://www.webthatworks.it