Re: Problem Designing Index

"Alan J Batsford" <AJBatsford(at)uss(dot)com> writes:

> Thanks for the help, after your email I went to capture some analyze output
> for you and when I did I figured to bump up the statistics on the two
> columns of interest from 100 to 1000. Now all statements return close to
> instantly.

Note that 1000 can take quite a lot of space in the statistics table. Make
sure it's vacuumed regularly and check that this isn't slowing down planning
of simple queries excessively.

Look at the explain analyze and check that the estimates are reasonably
accurate. They may have just flipped from being wildly inaccurate on the wrong
side of the decision point to wildly inaccurate but on the right side of the
decision point.

> Is this analyze tool something I need to run periodically to keep
> performance up? If so how often should I run it.

Yes. autovacuum likes to do it whenever 10% of the table has been updated, but
your mileage will vary considerably depending on how much your updates or
other DML affects the distribution which the queries are depending on.

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com