Really really slow select count(*)
| От | felix |
|---|---|
| Тема | Really really slow select count(*) |
| Дата | |
| Msg-id | AANLkTikp3_mvRx+NVvPEbUDcML15BDFdFfL6wPvRyKmd@mail.gmail.com обсуждение исходный текст |
| Ответ на | Really really slow select count(*) (felix <crucialfelix@gmail.com>) |
| Ответы |
Re: Really really slow select count(*)
|
| Список | pgsql-performance |
reply was meant for the list
---------- Forwarded message ----------
From: felix <crucialfelix@gmail.com>
Date: Fri, Feb 4, 2011 at 4:39 PM
Subject: Re: [PERFORM] Really really slow select count(*)
To: Greg Smith <greg@2ndquadrant.com>
SELECT relpages*8/1024 FROM pg_class
WHERE relname='fastadder_fastadderstatus';
From: felix <crucialfelix@gmail.com>
Date: Fri, Feb 4, 2011 at 4:39 PM
Subject: Re: [PERFORM] Really really slow select count(*)
To: Greg Smith <greg@2ndquadrant.com>
On Fri, Feb 4, 2011 at 3:56 PM, Greg Smith <greg@2ndquadrant.com> wrote:
PostgreSQL version? If you're running on 8.3 or earlier, I would be suspicous that your Free Space Map has been overrun.
8.3
What you are seeing is that the table itself is much larger on disk than it's supposed to be.
which part of the explain told you that ?
> shaun thomas
SELECT relpages*8/1024 FROM pg_class
WHERE relname='fastadder_fastadderstatus';
458MB
way too big. build_cache is text between 500-1k chars
That can be caused by frequent UPDATEs if you don't have vacuum cleanup working effectively, you'll get lots of dead sections left behind from UPDATEs in the middle.
ok, I just vacuumed it (did this manually a few times as well). and auto is on.
still:
32840.000ms
and still 458MB
The best way to fix all this is to run CLUSTER on the table.
http://www.postgresonline.com/journal/archives/10-How-does-CLUSTER-ON-improve-index-performance.html
now that would order the data on disk by id (primary key)
the usage of the table is either by a query or by position_in_queue which is rewritten often (I might change this part of the app and pull it out of this table)
is this definitely the best way to fix this ?
thanks for your help !
That will introduce a bit of downtime while it holds a lock on the table (only a few minutes based on what you've shown here), but the copy you'll have afterwards won't be spread all over disk anymore.-- Greg Smith 2ndQuadrant US greg@2ndQuadrant.com Baltimore, MD PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us "PostgreSQL 9.0 High Performance": http://www.2ndQuadrant.com/books
В списке pgsql-performance по дате отправления: