pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedcount(*) performance
17+ messages / 10 participants
[nested] [flat]
* count(*) performance
@ 2006-03-27 13:34 Gábriel Ákos <akos.gabriel@i-logic.hu>
2006-03-27 13:41 ` Re: count(*) performance Jim C. Nasby <jnasby@pervasive.com>
2006-03-27 16:31 ` Re: count(*) performance Luke Lonergan <llonergan@greenplum.com>
0 siblings, 2 replies; 17+ messages in thread
From: Gábriel Ákos @ 2006-03-27 13:34 UTC (permalink / raw)
To: pgsql-performance
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?
--
Üdvözlettel,
Gábriel Ákos
-=E-Mail :akos.gabriel@i-logic.hu|Web: http://www.i-logic.hu=-
-=Tel/fax:+3612367353 |Mobil:+36209278894 =-
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: count(*) performance
2006-03-27 13:34 count(*) performance Gábriel Ákos <akos.gabriel@i-logic.hu>
@ 2006-03-27 13:41 ` Jim C. Nasby <jnasby@pervasive.com>
2006-03-27 18:05 ` Re: count(*) performance Gábriel Ákos <akos.gabriel@i-logic.hu>
1 sibling, 1 reply; 17+ messages in thread
From: Jim C. Nasby @ 2006-03-27 13:41 UTC (permalink / raw)
To: G?briel ?kos <akos.gabriel@i-logic.hu>; +Cc: pgsql-performance
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/lp/newsletters/2006/Insights_postgres_Feb.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
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: count(*) performance
2006-03-27 13:34 count(*) performance Gábriel Ákos <akos.gabriel@i-logic.hu>
2006-03-27 13:41 ` Re: count(*) performance Jim C. Nasby <jnasby@pervasive.com>
@ 2006-03-27 18:05 ` Gábriel Ákos <akos.gabriel@i-logic.hu>
2006-03-27 18:14 ` Re: count(*) performance Luke Lonergan <llonergan@greenplum.com>
0 siblings, 1 reply; 17+ messages in thread
From: Gábriel Ákos @ 2006-03-27 18:05 UTC (permalink / raw)
To: 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.gabriel@i-logic.hu|Web: http://www.i-logic.hu=-
-=Tel/fax:+3612367353 |Mobil:+36209278894 =-
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: count(*) performance
2006-03-27 13:34 count(*) performance Gábriel Ákos <akos.gabriel@i-logic.hu>
2006-03-27 13:41 ` Re: count(*) performance Jim C. Nasby <jnasby@pervasive.com>
2006-03-27 18:05 ` Re: count(*) performance Gábriel Ákos <akos.gabriel@i-logic.hu>
@ 2006-03-27 18:14 ` Luke Lonergan <llonergan@greenplum.com>
2006-03-27 18:21 ` Re: count(*) performance Gábriel Ákos <akos.gabriel@i-logic.hu>
2006-03-27 19:20 ` Re: count(*) performance Brendan Duddridge <brendan@clickspace.com>
0 siblings, 2 replies; 17+ messages in thread
From: Luke Lonergan @ 2006-03-27 18:14 UTC (permalink / raw)
To: Gábriel Ákos <akos.gabriel@i-logic.hu>; pgsql-performance
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
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: count(*) performance
2006-03-27 13:34 count(*) performance Gábriel Ákos <akos.gabriel@i-logic.hu>
2006-03-27 13:41 ` Re: count(*) performance Jim C. Nasby <jnasby@pervasive.com>
2006-03-27 18:05 ` Re: count(*) performance Gábriel Ákos <akos.gabriel@i-logic.hu>
2006-03-27 18:14 ` Re: count(*) performance Luke Lonergan <llonergan@greenplum.com>
@ 2006-03-27 18:21 ` Gábriel Ákos <akos.gabriel@i-logic.hu>
2006-03-27 19:21 ` Re: count(*) performance Matthew T. O'Connor <matthew@zeut.net>
2006-03-28 11:49 ` Re: count(*) performance Markus Schaber <schabi@logix-tt.com>
1 sibling, 2 replies; 17+ messages in thread
From: Gábriel Ákos @ 2006-03-27 18:21 UTC (permalink / raw)
To: pgsql-performance
Luke Lonergan wrote:
> 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.
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 =-
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: count(*) performance
2006-03-27 13:34 count(*) performance Gábriel Ákos <akos.gabriel@i-logic.hu>
2006-03-27 13:41 ` Re: count(*) performance Jim C. Nasby <jnasby@pervasive.com>
2006-03-27 18:05 ` Re: count(*) performance Gábriel Ákos <akos.gabriel@i-logic.hu>
2006-03-27 18:14 ` Re: count(*) performance Luke Lonergan <llonergan@greenplum.com>
2006-03-27 18:21 ` Re: count(*) performance Gábriel Ákos <akos.gabriel@i-logic.hu>
@ 2006-03-27 19:21 ` Matthew T. O'Connor <matthew@zeut.net>
1 sibling, 0 replies; 17+ messages in thread
From: Matthew T. O'Connor @ 2006-03-27 19:21 UTC (permalink / raw)
To: Gábriel Ákos <akos.gabriel@i-logic.hu>; +Cc: pgsql-performance
Gábriel Ákos wrote:
> Luke Lonergan wrote:
>> 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.
>
> 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
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: count(*) performance
2006-03-27 13:34 count(*) performance Gábriel Ákos <akos.gabriel@i-logic.hu>
2006-03-27 13:41 ` Re: count(*) performance Jim C. Nasby <jnasby@pervasive.com>
2006-03-27 18:05 ` Re: count(*) performance Gábriel Ákos <akos.gabriel@i-logic.hu>
2006-03-27 18:14 ` Re: count(*) performance Luke Lonergan <llonergan@greenplum.com>
2006-03-27 18:21 ` Re: count(*) performance Gábriel Ákos <akos.gabriel@i-logic.hu>
@ 2006-03-28 11:49 ` Markus Schaber <schabi@logix-tt.com>
1 sibling, 0 replies; 17+ messages in thread
From: Markus Schaber @ 2006-03-28 11:49 UTC (permalink / raw)
To: Gábriel Ákos <akos.gabriel@i-logic.hu>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: count(*) performance
2006-03-27 13:34 count(*) performance Gábriel Ákos <akos.gabriel@i-logic.hu>
2006-03-27 13:41 ` Re: count(*) performance Jim C. Nasby <jnasby@pervasive.com>
2006-03-27 18:05 ` Re: count(*) performance Gábriel Ákos <akos.gabriel@i-logic.hu>
2006-03-27 18:14 ` Re: count(*) performance Luke Lonergan <llonergan@greenplum.com>
@ 2006-03-27 19:20 ` Brendan Duddridge <brendan@clickspace.com>
2006-03-27 19:35 ` Re: count(*) performance Alvaro Herrera <alvherre@commandprompt.com>
2006-03-27 19:43 ` Re: count(*) performance Guido Neitzer <guido.neitzer@pharmaline.de>
2006-03-28 17:26 ` Re: count(*) performance Jim C. Nasby <jnasby@pervasive.com>
1 sibling, 3 replies; 17+ messages in thread
From: Brendan Duddridge @ 2006-03-27 19:20 UTC (permalink / raw)
To: Luke Lonergan <llonergan@greenplum.com>; +Cc: "Gábriel Ákos" <akos.gabriel@i-logic.hu>; pgsql-performance
Does that mean that even though autovacuum is turned on, you still
should do a regular vacuum analyze periodically?
Thanks,
____________________________________________________________________
Brendan Duddridge | CTO | 403-277-5591 x24 | brendan@clickspace.com
ClickSpace Interactive Inc.
Suite L100, 239 - 10th Ave. SE
Calgary, AB T2G 0V9
http://www.clickspace.com
On Mar 27, 2006, at 11:14 AM, Luke Lonergan wrote:
> 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
>
Attachments:
smime.p7s (application/pkcs7-signature, 2.3K) download
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: count(*) performance
2006-03-27 13:34 count(*) performance Gábriel Ákos <akos.gabriel@i-logic.hu>
2006-03-27 13:41 ` Re: count(*) performance Jim C. Nasby <jnasby@pervasive.com>
2006-03-27 18:05 ` Re: count(*) performance Gábriel Ákos <akos.gabriel@i-logic.hu>
2006-03-27 18:14 ` Re: count(*) performance Luke Lonergan <llonergan@greenplum.com>
2006-03-27 19:20 ` Re: count(*) performance Brendan Duddridge <brendan@clickspace.com>
@ 2006-03-27 19:35 ` Alvaro Herrera <alvherre@commandprompt.com>
2 siblings, 0 replies; 17+ messages in thread
From: Alvaro Herrera @ 2006-03-27 19:35 UTC (permalink / raw)
To: Brendan Duddridge <brendan@clickspace.com>; +Cc: Luke Lonergan <llonergan@greenplum.com>; Gábriel Ákos <akos.gabriel@i-logic.hu>; pgsql-performance
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.
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: count(*) performance
2006-03-27 13:34 count(*) performance Gábriel Ákos <akos.gabriel@i-logic.hu>
2006-03-27 13:41 ` Re: count(*) performance Jim C. Nasby <jnasby@pervasive.com>
2006-03-27 18:05 ` Re: count(*) performance Gábriel Ákos <akos.gabriel@i-logic.hu>
2006-03-27 18:14 ` Re: count(*) performance Luke Lonergan <llonergan@greenplum.com>
2006-03-27 19:20 ` Re: count(*) performance Brendan Duddridge <brendan@clickspace.com>
@ 2006-03-27 19:43 ` Guido Neitzer <guido.neitzer@pharmaline.de>
2 siblings, 0 replies; 17+ messages in thread
From: Guido Neitzer @ 2006-03-27 19:43 UTC (permalink / raw)
To: Brendan Duddridge <brendan@clickspace.com>; +Cc: pgsql-performance
On 27.03.2006, at 21:20 Uhr, Brendan Duddridge wrote:
> Does that mean that even though autovacuum is turned on, you still
> should do a regular vacuum analyze periodically?
It seems that there are situations where autovacuum does not a really
good job.
However, in our application I have made stupid design decision which
I want to change as soon as possible. I have a "visit count" column
in one of the very large tables, so updates are VERY regular. I've
just checked and saw that autovacuum does a great job with that.
Nevertheless I have set up a cron job to do a standard vacuum every
month. I've used vacuum full only once after I did a bulk update of
about 200.000 rows ...
cug
--
PharmaLine, Essen, GERMANY
Software and Database Development
Attachments:
smime.p7s (application/pkcs7-signature, 2.4K) download
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: count(*) performance
2006-03-27 13:34 count(*) performance Gábriel Ákos <akos.gabriel@i-logic.hu>
2006-03-27 13:41 ` Re: count(*) performance Jim C. Nasby <jnasby@pervasive.com>
2006-03-27 18:05 ` Re: count(*) performance Gábriel Ákos <akos.gabriel@i-logic.hu>
2006-03-27 18:14 ` Re: count(*) performance Luke Lonergan <llonergan@greenplum.com>
2006-03-27 19:20 ` Re: count(*) performance Brendan Duddridge <brendan@clickspace.com>
@ 2006-03-28 17:26 ` Jim C. Nasby <jnasby@pervasive.com>
2 siblings, 0 replies; 17+ messages in thread
From: Jim C. Nasby @ 2006-03-28 17:26 UTC (permalink / raw)
To: Brendan Duddridge <brendan@clickspace.com>; +Cc: Luke Lonergan <llonergan@greenplum.com>; G?briel ?kos <akos.gabriel@i-logic.hu>; pgsql-performance
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
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: count(*) performance
2006-03-27 13:34 count(*) performance Gábriel Ákos <akos.gabriel@i-logic.hu>
@ 2006-03-27 16:31 ` Luke Lonergan <llonergan@greenplum.com>
2006-03-27 18:04 ` Re: count(*) performance Gábriel Ákos <akos.gabriel@i-logic.hu>
1 sibling, 1 reply; 17+ messages in thread
From: Luke Lonergan @ 2006-03-27 16:31 UTC (permalink / raw)
To: Gábriel Ákos <akos.gabriel@i-logic.hu>; pgsql-performance
Gabriel,
On 3/27/06 5:34 AM, "Gábriel Ákos" <akos.gabriel@i-logic.hu> wrote:
> 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?
Before changing anything with your Postgres configuration, you should check
your hard drive array performance. All select count(*) does is a sequential
scan of your data, and if the table is larger than memory, or if it's the
first time you've scanned it, it is limited by your disk speed.
To test your disk speed, use the following commands and report the times
here:
time bash -c "dd if=/dev/zero of=bigfile bs=8k count=500000 && sync"
time dd if=bigfile of=/dev/null bs=8k
If these are taking a long time, from another session watch the I/O rate
with "vmstat 1" for a while and report that here.
- Luke
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: count(*) performance
2006-03-27 13:34 count(*) performance Gábriel Ákos <akos.gabriel@i-logic.hu>
2006-03-27 16:31 ` Re: count(*) performance Luke Lonergan <llonergan@greenplum.com>
@ 2006-03-27 18:04 ` Gábriel Ákos <akos.gabriel@i-logic.hu>
0 siblings, 0 replies; 17+ messages in thread
From: Gábriel Ákos @ 2006-03-27 18:04 UTC (permalink / raw)
To: pgsql-performance
Luke Lonergan wrote:
> To test your disk speed, use the following commands and report the times
> here:
>
> time bash -c "dd if=/dev/zero of=bigfile bs=8k count=500000 && sync"
root@panther:/fast # time bash -c "dd if=/dev/zero of=bigfile bs=8k
count=500000 && sync"
500000+0 records in
500000+0 records out
4096000000 bytes transferred in 45.469404 seconds (90082553 bytes/sec)
real 0m56.880s
user 0m0.112s
sys 0m18.937s
> time dd if=bigfile of=/dev/null bs=8k
root@panther:/fast # time dd if=bigfile of=/dev/null bs=8k
500000+0 records in
500000+0 records out
4096000000 bytes transferred in 53.542147 seconds (76500481 bytes/sec)
real 0m53.544s
user 0m0.048s
sys 0m10.637s
I guess these values aren't that bad :)
--
Üdvözlettel,
Gábriel Ákos
-=E-Mail :akos.gabriel@i-logic.hu|Web: http://www.i-logic.hu=-
-=Tel/fax:+3612367353 |Mobil:+36209278894 =-
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: count(*) performance
@ 2006-03-27 21:57 Mikael Carneholm <Mikael.Carneholm@WirelessCar.com>
2006-03-27 22:43 ` Re: count(*) performance Matthew T. O'Connor <matthew@zeut.net>
0 siblings, 1 reply; 17+ messages in thread
From: Mikael Carneholm @ 2006-03-27 21:57 UTC (permalink / raw)
To: pgsql-performance
This is where a "last_vacuumed" (and "last_analyzed") column in
pg_statistic(?) would come in handy. Each time vacuum or analyze has
finished, update the row for the specific table that was
vacuumed/analyzed with a timestamp in the last_vacuumed/last_analyzed
column. No more guessing "maybe I haven't vacuumed/analyzed in a while",
and each time a user complains about bad performance, one could request
the user to do a "select s.last_vacuumed, s.last_analyzed from
pg_statistic s, pg_attribute a, pg_class c where ..."
It SOUNDS easy to implement, but that has fooled me before... :-)
- Mikael
-----Original Message-----
From: pgsql-performance-owner@postgresql.org
[mailto:pgsql-performance-owner@postgresql.org]On Behalf Of Guido
Neitzer
Sent: den 27 mars 2006 21:44
To: Brendan Duddridge
Cc: Postgresql Performance
Subject: Re: [PERFORM] count(*) performance
On 27.03.2006, at 21:20 Uhr, Brendan Duddridge wrote:
> Does that mean that even though autovacuum is turned on, you still
> should do a regular vacuum analyze periodically?
It seems that there are situations where autovacuum does not a really
good job.
However, in our application I have made stupid design decision which
I want to change as soon as possible. I have a "visit count" column
in one of the very large tables, so updates are VERY regular. I've
just checked and saw that autovacuum does a great job with that.
Nevertheless I have set up a cron job to do a standard vacuum every
month. I've used vacuum full only once after I did a bulk update of
about 200.000 rows ...
cug
--
PharmaLine, Essen, GERMANY
Software and Database Development
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: count(*) performance
2006-03-27 21:57 Re: count(*) performance Mikael Carneholm <Mikael.Carneholm@WirelessCar.com>
@ 2006-03-27 22:43 ` Matthew T. O'Connor <matthew@zeut.net>
2006-03-27 23:13 ` Re: count(*) performance Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 17+ messages in thread
From: Matthew T. O'Connor @ 2006-03-27 22:43 UTC (permalink / raw)
To: Mikael Carneholm <Mikael.Carneholm@WirelessCar.com>; +Cc: pgsql-performance
Mikael Carneholm wrote:
> This is where a "last_vacuumed" (and "last_analyzed") column in
> pg_statistic(?) would come in handy. Each time vacuum or analyze has
> finished, update the row for the specific table that was
> vacuumed/analyzed with a timestamp in the last_vacuumed/last_analyzed
> column. No more guessing "maybe I haven't vacuumed/analyzed in a while",
> and each time a user complains about bad performance, one could request
> the user to do a "select s.last_vacuumed, s.last_analyzed from
> pg_statistic s, pg_attribute a, pg_class c where ..."
>
> It SOUNDS easy to implement, but that has fooled me before... :-)
It is fairly easy to implement, however it has been discussed before and
decided that it wasn't necessary. What the system cares about is how
long it's been since the last vacuum in terms of XIDs not time. Storing
a timestamp would make it more human readable, but I'm not sure the
powers that be want to add two new columns to some system table to
accommodate this.
Matt
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: count(*) performance
2006-03-27 21:57 Re: count(*) performance Mikael Carneholm <Mikael.Carneholm@WirelessCar.com>
2006-03-27 22:43 ` Re: count(*) performance Matthew T. O'Connor <matthew@zeut.net>
@ 2006-03-27 23:13 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 17+ messages in thread
From: Tom Lane @ 2006-03-27 23:13 UTC (permalink / raw)
To: Matthew T. O'Connor <matthew@zeut.net>; +Cc: Mikael Carneholm <Mikael.Carneholm@WirelessCar.com>; pgsql-performance
"Matthew T. O'Connor" <matthew@zeut.net> writes:
> It is fairly easy to implement, however it has been discussed before and
> decided that it wasn't necessary. What the system cares about is how
> long it's been since the last vacuum in terms of XIDs not time.
I think Alvaro is intending to do the latter (store per-table vacuum xid
info) for 8.2.
regards, tom lane
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: count(*) performance
@ 2006-03-27 23:17 Mikael Carneholm <Mikael.Carneholm@WirelessCar.com>
0 siblings, 0 replies; 17+ messages in thread
From: Mikael Carneholm @ 2006-03-27 23:17 UTC (permalink / raw)
To: Matthew T. O'Connor <matthew@zeut.net>; +Cc: pgsql-performance
I think it is definitely necessary from an administration point of view - as an administrator, I want to know:
1) Are there any stats (at all) in a schema
2) Are there any stats on the table that slow_query_foo is targeting
3) If I have stats, how recent are they
4) Could it be that there are a lot of dead tuples lying around (given the amount of traffic I know I have)
These would be (are always!) the first questions I ask myself when I'm about to identify performance problems in an app, don't know how other people do though :)
Maybe something I'll try to look into this weekend, if I can spare some time.
- Mikael
-----Original Message-----
From: Matthew T. O'Connor [mailto:matthew@zeut.net]
Sent: den 28 mars 2006 00:43
To: Mikael Carneholm
Cc: Postgresql Performance
Subject: Re: [PERFORM] count(*) performance
Mikael Carneholm wrote:
> This is where a "last_vacuumed" (and "last_analyzed") column in
> pg_statistic(?) would come in handy. Each time vacuum or analyze has
> finished, update the row for the specific table that was
> vacuumed/analyzed with a timestamp in the last_vacuumed/last_analyzed
> column. No more guessing "maybe I haven't vacuumed/analyzed in a while",
> and each time a user complains about bad performance, one could request
> the user to do a "select s.last_vacuumed, s.last_analyzed from
> pg_statistic s, pg_attribute a, pg_class c where ..."
>
> It SOUNDS easy to implement, but that has fooled me before... :-)
It is fairly easy to implement, however it has been discussed before and
decided that it wasn't necessary. What the system cares about is how
long it's been since the last vacuum in terms of XIDs not time. Storing
a timestamp would make it more human readable, but I'm not sure the
powers that be want to add two new columns to some system table to
accommodate this.
Matt
^ permalink raw reply [nested|flat] 17+ messages in thread
end of thread, other threads:[~2006-03-28 17:26 UTC | newest]
Thread overview: 17+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-03-27 13:34 count(*) performance Gábriel Ákos <akos.gabriel@i-logic.hu>
2006-03-27 13:41 ` Jim C. Nasby <jnasby@pervasive.com>
2006-03-27 18:05 ` Gábriel Ákos <akos.gabriel@i-logic.hu>
2006-03-27 18:14 ` Luke Lonergan <llonergan@greenplum.com>
2006-03-27 18:21 ` Gábriel Ákos <akos.gabriel@i-logic.hu>
2006-03-27 19:21 ` Matthew T. O'Connor <matthew@zeut.net>
2006-03-28 11:49 ` Markus Schaber <schabi@logix-tt.com>
2006-03-27 19:20 ` Brendan Duddridge <brendan@clickspace.com>
2006-03-27 19:35 ` Alvaro Herrera <alvherre@commandprompt.com>
2006-03-27 19:43 ` Guido Neitzer <guido.neitzer@pharmaline.de>
2006-03-28 17:26 ` Jim C. Nasby <jnasby@pervasive.com>
2006-03-27 16:31 ` Luke Lonergan <llonergan@greenplum.com>
2006-03-27 18:04 ` Gábriel Ákos <akos.gabriel@i-logic.hu>
2006-03-27 21:57 Re: count(*) performance Mikael Carneholm <Mikael.Carneholm@WirelessCar.com>
2006-03-27 22:43 ` Matthew T. O'Connor <matthew@zeut.net>
2006-03-27 23:13 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-03-27 23:17 Re: count(*) performance Mikael Carneholm <Mikael.Carneholm@WirelessCar.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox