Re: CREATE INDEX speeds up query on 31 row table ...

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(at)mit(dot)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(at)postgresql(dot)org)
>

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