Re: REINDEX takes half a day (and still not complete!)

On Fri, Mar 18, 2011 at 9:07 PM, Phoenix Kiula <phoenix(dot)kiula(at)gmail(dot)com> wrote:
> 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

What is the output of running vacuum verbose as a superuser (you can
run it on the postgres database so it returns fast.) We're looking
for the output that looks like this:

INFO: free space map contains 1930193 pages in 749 relations
DETAIL: A total of 1787744 page slots are in use (including overhead).
1787744 page slots are required to track all free space.
Current limits are: 10000000 page slots, 3000 relations, using 58911 kB.

If the space needed exceeds page slots then you need to crank up your
free space map. If the relations exceeds the available then you'll
need to crank up max relations.