Re: CREATE INDEX speeds up query on 31 row table ... - Mailing list pgsql-hackers

From Marc G. Fournier
Subject Re: CREATE INDEX speeds up query on 31 row table ...
Date
Msg-id 20040930200356.I23868@ganymede.hub.org
Whole thread Raw
In response to Re: CREATE INDEX speeds up query on 31 row table ...  ("Matthew T. O'Connor" <matthew@zeut.net>)
List pgsql-hackers
BTW, seems to be holding up pretty well so far, but I've also reduced, by 
half, the baner ads on archives, so its not being hit near as much as it 
used to be ...

du 17144
6562    17144



On Thu, 30 Sep 2004, Matthew T. O'Connor wrote:

> Tom Lane wrote:
>
>> Greg Stark <gsstark@mit.edu> writes:
>> 
>>> You say it's "*very* busy" is it possible there are hundreds or thousands 
>>> of
>>> tuples in there that are uncommitted or committed after this query starts?
>>> 
>> More specifically, I bet there's a huge number of completely empty
>> pages, which would be read by a seqscan but not an indexscan.  VACUUM
>> FULL should fix it nicely, but it's odd that autovacuum isn't keeping
>> a lid on the file size.  Maybe with so few live rows, it's confused into
>> thinking it doesn't need to vacuum the table often?
>> 
> I think autovacuum is keeping a lid on the file size, but the lid is too 
> loose.  The default values for autovacuum were intentionally set a little 
> conservative so that it wouldn't cause a net slowdown by vacuuming too often. 
> Given that, for a 31 row table, the default autovacuum settings say to vacuum 
> every 1062 updates (or deletes), depending on the size of the tuples that 
> could be a lot of dead space.  Also, the default sleep time is 5 minutes, 
> given your logs, autovacuum feels the need to do something to your table 
> every time it wakes up so I think you are pushing the envelope.
>
> Are you using default values for autovacuum?   Could you prove a little more 
> detail by setting pg_autovacuum  debug with -d 2
>
> Matthew
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>   (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>

----
Marc G. Fournier           Hub.Org Networking Services (http://www.hub.org)
Email: scrappy@hub.org           Yahoo!: yscrappy              ICQ: 7615664


pgsql-hackers by date:

Previous
From: "Marc G. Fournier"
Date:
Subject: Re: CREATE INDEX speeds up query on 31 row table ...
Next
From: Tom Lane
Date:
Subject: Re: Reviving Time Travel (was Re: 'TID index')