On Fri, Mar 18, 2011 at 9:07 PM, Phoenix Kiula <phoenix.kiula@xxxxxxxxx> 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. -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
