From: Alvaro Herrera <alvherre@commandprompt.com>
To: Ron St-Pierre <ron.pgsql@shaw.ca>
Cc: pgsql-performance@postgresql.org
Subject: Re: 12 hour table vacuums
Date: Tue, 23 Oct 2007 15:22:02 -0300
Message-ID: <20071023182202.GM18013@alvh.no-ip.org> (raw)
In-Reply-To: <471E2895.4030601@shaw.ca>
References: <471E18ED.3090704@shaw.ca>
<20071023161204.GK18013@alvh.no-ip.org>
<471E2895.4030601@shaw.ca>
Ron St-Pierre wrote:
> Alvaro Herrera wrote:
>> Ron St-Pierre wrote:
>>
>>
>>> Okay, here's our system:
>>> postgres 8.1.4
>>>
>>
>> Upgrade to 8.1.10
>>
> Any particular fixes in 8.1.10 that would help with this?
I don't think so, but my guess is that you really want to avoid the
autovacuum bug which makes it vacuum without FREEZE on template0, that
has caused so many problems all over the planet.
>>> Here's the table information:
>>> The table has 140,000 rows, 130 columns (mostly NUMERIC), 60 indexes.
>>
>> 60 indexes? You gotta be kidding. You really have 60 columns on which
>> to scan?
>>
>>
> Really. 60 indexes. They're the most commonly requested columns for company
> information (we believe). Any ideas on testing our assumptions about that?
> I would like to know definitively what are the most popular columns. Do you
> think that rules would be a good approach for this? (Sorry if I'm getting
> way off topic here)
As Josh Drake already said, you can check pg_stat* views to see which
indexes are not used. Hard to say anything else without seeing the
definition.
--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: alvherre@commandprompt.com, ron.pgsql@shaw.ca
Subject: Re: 12 hour table vacuums
In-Reply-To: <20071023182202.GM18013@alvh.no-ip.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox