Skip site navigation (1) Skip section navigation (2)

Re: count(*) performance

From: Gábriel Ákos <akos(dot)gabriel(at)i-logic(dot)hu>
To: pgsql-performance(at)postgresql(dot)org
Subject: Re: count(*) performance
Date: 2006-03-27 18:05:31
Message-ID: 4428296B.9040505@i-logic.hu (view raw or flat)
Thread:
Lists: pgsql-performance
Jim C. Nasby wrote:

> But in this case, I'd bet money that if it's taking 4 minutes something
> else is wrong. Have you been vacuuming that table frequently enough?

That gave me an idea. I thought that autovacuum is doing it right, but I
issued a vacuum full analyze verbose , and it worked all the day.
After that I've tweaked memory settings a bit too (more fsm_pages)

Now:

staging=# SELECT count(*) from infx.infx_product;
   count
---------
  3284997
(1 row)

Time: 1301.049 ms

As I saw the output, the database was compressed to 10% of its size :)
This table has quite big changes every 4 hour, let's see how it works.
Maybe I'll have to issue full vacuums from cron regularly.

> What's SELECT relpages FROM pg_class WHERE relname='tablename' show?

This went to 10% as well, now it's around 156000 pages.

Regards,
Akos


-- 
Üdvözlettel,
Gábriel Ákos
-=E-Mail :akos(dot)gabriel(at)i-logic(dot)hu|Web:  http://www.i-logic.hu=-
-=Tel/fax:+3612367353            |Mobil:+36209278894            =-


In response to

Responses

pgsql-performance by date

Next:From: Luke LonerganDate: 2006-03-27 18:14:45
Subject: Re: count(*) performance
Previous:From: Gábriel ÁkosDate: 2006-03-27 18:04:46
Subject: Re: count(*) performance

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group