Yeah I should have mentioned...adding a where clause is always my use case when using count().
So to clarify my question: does anyone have any experience with count() (with a WHERE clause) in Postgres 9.2?
On Oct 17, 2012 11:42 AM, "Simon Riggs" <
simon@2ndquadrant.com> wrote:
On 17 October 2012 16:00, Steve Horn <steve@stevehorn.cc> wrote:
> One of the reasons that my team could not take advantage of PostgreSQL was
> due to the poor performance of count(*) aggregate function.
>
> I visited the "Slow Counting" page on the wiki
> (http://wiki.postgresql.org/wiki/Slow_Counting) and it states:
> "Note that the following article only applies to versions of PostgreSQL
> prior to 9.2. Index-only scans are now implemented."
>
> So does this mean that count() is no longer slow? If not, how much faster is
> it? And what does it mean when it says "Index-only scans are now
> implemented."?
I think someone's been drinking the cool-aid...
YMMV but its possible in some cases to get 2-3 times previous
performance on larger, relatively static tables. Not much effect on
smaller, regularly updated tables. Given that most people doing
count() run them either with a WHERE clause or on tables that change
enough you need to re-execute the query, you might not see much in
real usage.
--
Simon Riggs http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services