Thread: Tsearch2 index size

Tsearch2 index size

From
richard@armchair.mb.ca
Date:
I am running versions 8.1.2 and I installed 8.2B last week.  I dumped 
the data from a rather large (several million records) from the old into the 
new version.

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 the 
tsearch2 performance?

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

Richard Whidden



Re: Tsearch2 index size

From
Jeff Davis
Date:
On Mon, 2006-10-23 at 13:36 -0500, richard@armchair.mb.ca wrote:
> 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 the 
> tsearch2 performance?

This might be due to the new FILLFACTOR feature in 8.2. This parameter
defaults to 90. If you set it to 100, then you should get similar
results as in 8.1.

I don't know much about the new GIN indexing, but from what I've read I
would expect the index to actually be smaller. 

Regards,Jeff Davis