On Sun, Aug 04, 2002 at 01:51:55AM -0400, Tom Lane wrote:
> Steve Atkins <steve@blighty.com> writes:
> > So is 15-20 seconds for \d unusual?
>
> Very.
>
> > If so, where should I start looking to see what's broken?
>
> Well, what query plans are used for the queries issued by \d on each
> of your two boxes? Have you done ANALYZE or VACUUM ANALYZE lately?
> (Out-of-date stats about the system tables might lead the planner to
> make a stupid choice of query plan.)
That was the problem. I'd been happily running analyze - but not as
the postgres user, so it was looking only at the user tables, not the
system tables.
The query times went from 6.5s to 1.2ms. Noticably better...
Thanks. Y'know, the help and support I've seen from Tom Lane on this
list alone exceeds everything I've ever seen from Oracle.
I'm using postgres for a long-running application[1], where I'd really
rather the application daemons don't have any access as the postgres
user. As the schema doesn't change is it safe to analyze the system
tables as the postgres user once at installation time or should I
be doing that as part of the normal cronjobbed maintenance?
Cheers,
Steve
[1] Commercial, with pretty high data rates. I'm describing it as "based
on the industry standard PostgreSQL database" to customers and'll be
adding it to the list of PostrgreSQL apps once the press releases
go out.