Skip site navigation (1) Skip section navigation (2)

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

From: "Matthew T(dot) O'Connor" <matthew(at)zeut(dot)net>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Greg Stark <gsstark(at)mit(dot)edu>, pgsql-hackers(at)postgresql(dot)org
Subject: Re: CREATE INDEX speeds up query on 31 row table ...
Date: 2004-09-30 20:42:10
Message-ID: 415C6FA2.6040305@zeut.net (view raw or flat)
Thread:
Lists: pgsql-hackers
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


In response to

Responses

pgsql-hackers by date

Next:From: Tom LaneDate: 2004-09-30 20:43:17
Subject: Re: Bug in Beta3 with parser?
Previous:From: Tom LaneDate: 2004-09-30 20:41:46
Subject: Re: More pgindent bizarreness

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group