Thread: Tsearch2 index size

I am running versions 8.1.2 and I installed 8.2B last week.  I dumped 
the data from the old version into the new version. The db consists of 
several million records.  Total disk usage is approximately 114GB.

My two observations are as follows... Also, keep in mind these are truly 
just observations, I didn't use any benchmarking tools.  If someone can 
point me to a link of performance tools, I'd be happy to try them and report 
back!

1. The release notes indicate "more efficient vacuuming."  However, both 
vacuums seems to take about the same amount of time, ie. approx: 9 hours.  
Does "more efficient" simply mean, less IO/CPU busyness?  This one doesn't 
really bother me, the next one does...

Here are my vacuum parms, I used the same ones for both versions, of 
course.
----------
maintenance_work_mem = 400000 # Unnecessarily high, I know.... I left it                             # for comparison's
sake.
vacuum_cost_delay = 50
vacuum_cost_page_hit = 1
vacuum_cost_page_miss = 10
vacuum_cost_page_dirty = 20
vacuum_cost_limit = 2000
----------



2. I have a tsearch2 index which is 756MB in size in 8.1.2 but balloons to 
910MB in 8.2!  These numbers were taken right after a REINDEX.  Normally, I 
wouldn't care about physical index size, but this particular index is 
sitting on a ramdisk, so size really does matter.  I see that the tsearch2 
type was diddled with in 8.2.  Is this an intentional change to improve 
tsearch2 performance?

Thank you for any advice or abuse you give.  No.  Wait.  No abuse please.

Richard Whidden

richard-pgodbc@armchair.mb.ca writes:
> 1. The release notes indicate "more efficient vacuuming."  However, both 
> vacuums seems to take about the same amount of time, ie. approx: 9 hours.  

I think the improvements were only in btree index vacuuming, which it
sounds like isn't your big problem.

> 2. I have a tsearch2 index which is 756MB in size in 8.1.2 but balloons to 
> 910MB in 8.2!

FILLFACTOR?
        regards, tom lane

> 
>> 2. I have a tsearch2 index which is 756MB in size in 8.1.2 but balloons to 
>> 910MB in 8.2!
> 
> FILLFACTOR?
> 

Tom, 

Of course!  I had it in my head that fillfactor had to be explicitely set.  
But then, after RTFM, it looks like there are defaults!  Thank you!

One more inane question, though.  The default fillfactors are currently 
#define'ed.  Is there or will there be a way to set the default fillfactor, 
using a system table perhaps?  I realize this is just a convenience, not 
integral to fillfactor functionality...

I sniffed around the docs and mailing lists but I could not find an answer.  My apologies if this question has been
answeredalready.
 

Richard Whidden