PostgreSQL Performance - Re: count(*) performance

Author

Re: count(*) performance

Jim C. Nasby

2006-03-27, 9:40 am

On Mon, Mar 27, 2006 at 03:34:32PM +0200, G?briel ?kos wrote:
> Hi,
>
> I guess this is an age-old 100times answered question, but I didn't find
> the answer to it yet (neither in the FAQ nor in the mailing list archives).
>
> Question: I have a table with 2.5M rows. count(*) on this table is
> running 4 minutes long. (dual opteron, 4gig ram, db on 4 disk raid10
> array (sata, not scsi)) Is this normal? How could I make it run faster?
> Maybe make it run faster for the 2nd time? Which parameters should I
> change in postgresql.conf and how?

First, count(*) on PostgreSQL tends to be slow because you can't do
index covering[1].

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?
What's SELECT relpages FROM pg_class WHERE relname='tablename' show?

[1] http://www.pervasive-postgres.com/l...e
b.asp#5
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Gábriel Ákos

2006-03-27, 1:37 pm

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.gabriel@i-logic.hu|Web: http://www.i-logic.hu=-
-=Tel/fax:+3612367353 |Mobil:+36209278894 =-

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

Luke Lonergan

2006-03-27, 1:37 pm

Gabriel,

On 3/27/06 10:05 AM, "Gábriel Ákos" <akos.gabriel@i-logic.hu> wrote:

> 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)

Oops! I replied to your disk speed before I saw this.

The only thing is - you probably don't want to do a "vacuum full", but
rather a simple "vacuum" should be enough.

- Luke

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Gábriel Ákos

2006-03-27, 1:37 pm

Luke Lonergan wrote:
> Gabriel,
>
> On 3/27/06 10:05 AM, "Gábriel Ákos" <akos.gabriel@i-logic.hu> wrote:
>
>
> Oops! I replied to your disk speed before I saw this.
>
> The only thing is - you probably don't want to do a "vacuum full", but
> rather a simple "vacuum" should be enough.

I thought that too. Autovacuum is running on our system but it didn't do
the trick. Anyway the issue is solved, thank you all for helping. :)

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

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Brendan Duddridge

2006-03-27, 1:37 pm

Matthew T. O'Connor

2006-03-27, 1:37 pm

Gábriel Ákos wrote:
> Luke Lonergan wrote:
>
> I thought that too. Autovacuum is running on our system but it didn't do
> the trick. Anyway the issue is solved, thank you all for helping. :)

Yeah, it would be nice of autovacuum had some way of raising a flag to
the admin that given current settings (thresholds, FSM etc...), it's not
keeping up with the activity. I don't know how to do this, but I hope
someone else has some good ideas.

Matt

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql
.org so that your
message can get through to the mailing list cleanly

Alvaro Herrera

2006-03-27, 1:37 pm

Brendan Duddridge wrote:
> Does that mean that even though autovacuum is turned on, you still
> should do a regular vacuum analyze periodically?

No, it probably means you have set FSM settings too low, or not tuned
the autovacuum parameters to your specific situation.

A bug in the autovacuum daemon is not unexpected however, so if it
doesn't work after tuning, let us know.

--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Guido Neitzer

2006-03-27, 8:28 pm

Markus Schaber

2006-03-28, 7:31 am

Gábriel Ákos wrote:

> I thought that too. Autovacuum is running on our system but it didn't do
> the trick. Anyway the issue is solved, thank you all for helping. :)

Hi, Gabriel, it may be that your Free Space Map (FSM) setting is way to
low.

Try increasing it.

Btw, VACUUM outputs a Warning if FSM is not high enough, maybe you can
find useful hints in the log file.

HTH
Markus

--
Markus Schaber | Logical Tracking&Tracing International AG
Dipl. Inf. | Software Development GIS

Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Jim C. Nasby

2006-03-28, 11:32 am

On Mon, Mar 27, 2006 at 12:20:54PM -0700, Brendan Duddridge wrote:
> Does that mean that even though autovacuum is turned on, you still
> should do a regular vacuum analyze periodically?

Doing a periodic vacuumdb -avz and keeping an eye on the last few lines
isn't a bad idea. It would also be helpful if there was a log parser
that could take a look at the output of a vacuumdb -av and look for any
problem areas, such as relations that have a lot of free space in them.
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Sponsored Links