Thread: PgAdmin startup query VERY slow
I have reported this on the pgadmin-support mailing list, but Andreas Pflug has asked me to post it here. With a particular database, PgAdmin3 takes a very long time to connect to a database. this is not a general problem with PgAdmin, but only with one database out of many. Other databases do not have the problem. And only with one particular server. The exact same database on a different server does not have the problem. The server in question is running PostgreSQL 7.3.2 on sparc-sun-solaris2.8, compiled by GCC 2.95.2 The other server which has the same database is running Postgres 7.3.4 on i386-redhat-linux-gnu, complied by GCC i386-redhat-linux-gcc 3.2.2. I have attached the query that Andreas says is the one that is run when PgAdmin first connects to a database as well as the output from running the query with explain turned on. Both Andreas and I would be every interested if this group might have any ideas why the query is so slow. NOTE: I have vacuumed the database, but that did not affect the timing at all. NOTE: The startup on the sparc server is 44 seconds, The startup on the linux server is 5 seconds. Andreas writes: I can't see too much from this query plan, it just seems you have 321 triggers an 4750 dependencies which isn't too extraordinary much. But 48 seconds execution time *is* much. Please repost this to pgsql-performance, including the query, backend version, and modified server settings. I'm not deep enough in planner items to analyze this sufficiently. Please let me CCd on this topic so I can see what I should change in pgAdmin3 (if any). --- Michael --- Michael
Attachment
Michael, > With a particular database, PgAdmin3 takes a very long time to connect to a > database. this is not a general problem with PgAdmin, but only with one > database out of many. Other databases do not have the problem. And only > with one particular server. The exact same database on a different server > does not have the problem. Have you run VACUUM ANALYZE *as the superuser* on the faulty server recently? From the look of the explain, PG is grossly underestimating the number of items in the pg_trigger and pg_depend tables, and thus choosing an inappropriate nested loop execution. -- -Josh Berkus Aglio Database Solutions San Francisco
That seemed to fix it. What does VACUUM ANALYZE do that VACUUM FULL does not? What causes a database to need vacuuming? At 01:01 PM 1/6/2004 -0800, Josh Berkus wrote: >Michael, > > > With a particular database, PgAdmin3 takes a very long time to connect > to a > > database. this is not a general problem with PgAdmin, but only with one > > database out of many. Other databases do not have the problem. And only > > with one particular server. The exact same database on a different server > > does not have the problem. > >Have you run VACUUM ANALYZE *as the superuser* on the faulty server >recently? > >From the look of the explain, PG is grossly underestimating the number of >items in the pg_trigger and pg_depend tables, and thus choosing an >inappropriate nested loop execution. > >-- >-Josh Berkus > Aglio Database Solutions > San Francisco --- Michael
Mark, > That seemed to fix it. What does VACUUM ANALYZE do that VACUUM FULL does > not? What causes a database to need vacuuming? See the Online Docs: http://www.postgresql.org/docs/current/static/maintenance.html Incidentally, just ANALYZE would probably have fixed your problem. Please do suggest to the PGAdmin team that they add a FAQ item about this. -- -Josh Berkus Aglio Database Solutions San Francisco
Josh Berkus <josh@agliodbs.com> writes:
> Incidentally, just ANALYZE would probably have fixed your problem.
... or just VACUUM; that would have updated the row count which is all
that was really needed here. The main point is that you do have to do
that as superuser, since the same commands issued as a non-superuser
won't touch the system tables (or any table you do not own).
regards, tom lane