DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=mime-version:date:message-id:subject:from:to:content-type;
	b=h0pbvM0IBClT8KpGSl+z+MhvHIW1NzsUXA/4QtegvmDs+LU99LilWkc3BwXxmS7EXO
	JYb4k1z1+4NZUkirvKGojhDV6KUcXaNe0dMYzD87tZpbroU4f+NeBvC4ad8Y9AbsCkuw
	XNCGVBLbrniEprcIwnNzWq+P/46tKJIDNWONs=
MIME-Version: 1.0
Date: Sat, 19 Mar 2011 11:07:33 +0800
Message-ID: <AANLkTim1aO2hG+D4h_VFXas8GTL6TQMLj7aDc7tJ0vok@mail.gmail.com>
Subject: REINDEX takes half a day (and still not complete!)
From: Phoenix Kiula <phoenix.kiula@gmail.com>
To: pgsql-performance@postgresql.org
Content-Type: text/plain; charset=ISO-8859-1

I have a large table but not as large as the kind of numbers that get
discussed on this list. It has 125 million rows.

REINDEXing the table takes half a day, and it's still not finished.

To write this post I did "SELECT COUNT(*)", and here's the output -- so long!

    select count(*) from links;
       count
    -----------
     125418191
    (1 row)

    Time: 1270405.373 ms

That's 1270 seconds!

I suppose the vaccuum analyze is not doing its job? As you can see
from settings below, I have autovacuum set to ON, and there's also a
cronjob every 10 hours to do a manual vacuum analyze on this table,
which is largest.

PG is version 8.2.9.

Any thoughts on what I can do to improve performance!?

Below are my settings.


max_connections              = 300
shared_buffers               = 500MB
effective_cache_size         = 1GB
max_fsm_relations            = 1500
max_fsm_pages                = 950000

work_mem                     = 100MB
temp_buffers                 = 4096
authentication_timeout       = 10s
ssl                          = off
checkpoint_warning           = 3600
random_page_cost             = 1

autovacuum                   = on
autovacuum_vacuum_cost_delay = 20

vacuum_cost_delay            = 20
vacuum_cost_limit            = 600

autovacuum_naptime           = 10
stats_start_collector        = on
stats_row_level              = on
autovacuum_vacuum_threshold  = 75
autovacuum_analyze_threshold = 25
autovacuum_analyze_scale_factor  = 0.02
autovacuum_vacuum_scale_factor   = 0.01

wal_buffers                  = 64
checkpoint_segments          = 128
checkpoint_timeout           = 900
fsync                        = on
maintenance_work_mem         = 512MB