Thread: Frequency of Analyze?

Frequency of Analyze?

From
Benjamin Smith
Date:
I have a rapidly growing database with a very complex schema, and I'm looking
to improve performance. It's typical to have 2-4 foreign keys in each table,
and there are currently 113 tables, and queries with 5-10 tables with
combined inner/outer joins are pretty typical. (I avoid subqueries anywhere I
can)

So far, my guiding philosophy has been "constrain everything" with primary
keys, unique, foreign keys and the like, relying on the implicit indexes and
the query scheduler to handle things, and so far, it's worked very well.

The database has grown in total size (calculated by the size of a pg_dump) 25%
in the past month, and the growth rate seems to be accellerating. (yikes!) We
are buying new equipment now, but I'd still like to optimize as best as
possible.

A few questions:

1) Any problem with running "Analyze" hourly via cron?

2) Does "Vacuum analyze" also do the actions performed by "Analyze"?

3) What's the best way to handle indexes when only 1 index is used per table
in a query, but there are several due to the various constraints on it? Is
there a way to benefit from all of these other indexes somehow? Is there a
definitive, detailed book for optimizing PG queries?

-Ben

--
"The best way to predict the future is to invent it."
- XEROX PARC slogan, circa 1978

Re: Frequency of Analyze?

From
"Jim C. Nasby"
Date:
On Fri, Oct 28, 2005 at 01:47:49PM -0700, Benjamin Smith wrote:
> 1) Any problem with running "Analyze" hourly via cron?

Probably not. Analyze usually only reads a small portion of the table.

> 2) Does "Vacuum analyze" also do the actions performed by "Analyze"?

Yes, it does.

> 3) What's the best way to handle indexes when only 1 index is used per table
> in a query, but there are several due to the various constraints on it? Is
> there a way to benefit from all of these other indexes somehow? Is there a
> definitive, detailed book for optimizing PG queries?

8.1 can utilize multiple indexes per table.
--
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461

Re: Frequency of Analyze?

From
Benjamin Smith
Date:
Wow.

Does it really produce the expected (probably dramatic, in my case!)
improvement in performance? (I'll be trying it out anyway..., but I'd love
your feedback)

How stable is it? Looks like I have a PG upgrade in my near future...

-Ben

On Friday 28 October 2005 14:51, you wrote:
> > 3) What's the best way to handle indexes when only 1 index is used per
table
> > in a query, but there are several due to the various constraints on it? Is
> > there a way to benefit from all of these other indexes somehow? Is there a
> > definitive, detailed book for optimizing PG queries?
>
> 8.1 can utilize multiple indexes per table.

--
"The best way to predict the future is to invent it."
- XEROX PARC slogan, circa 1978