Re: [PERFORM] PgAdmin startup query VERY slow

2004-01-06 Thread Tom Lane
Josh Berkus <[EMAIL PROTECTED]> 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

---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster


Re: [PERFORM] PgAdmin startup query VERY slow

2004-01-06 Thread Josh Berkus
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


---(end of broadcast)---
TIP 6: Have you searched our list archives?

   http://archives.postgresql.org


Re: [PERFORM] PgAdmin startup query VERY slow

2004-01-06 Thread Michael Shapiro
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 

---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]


Re: [PERFORM] PgAdmin startup query VERY slow

2004-01-06 Thread Josh Berkus
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


---(end of broadcast)---
TIP 6: Have you searched our list archives?

   http://archives.postgresql.org


[PERFORM] PgAdmin startup query VERY slow

2004-01-06 Thread Michael Shapiro
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 SELECT COUNT(*) FROM
(SELECT tgargs from pg_trigger tr
LEFT JOIN pg_depend dep ON dep.objid=tr.oid AND deptype = 'i'
LEFT JOIN pg_constraint co ON refobjid = co.oid AND contype = 'f'
WHERE co.oid IS NULL
GROUP BY tgargs
HAVING count(1) = 3) AS foo
QUERY PLAN
   { AGG 
   :startup_cost 183.36 
   :total_cost 183.36 
   :rows 1 
   :width 22 
   :qptargetlist (
  { TARGETENTRY 
  :resdom 
 { RESDOM 
 :resno 1 
 :restype 20 
 :restypmod -1 
 :resname count 
 :reskey 0 
 :reskeyop 0 
 :ressortgroupref 0 
 :resjunk false 
 }
   
  :expr 
 { AGGREG 
 :aggfnoid 2147 
 :aggtype 20 
 :target 
{ CONST 
:consttype 23 
:constlen 4 
:constbyval true 
:constisnull false 
:constvalue  4 [ 0 0 0 1 ] 
}
  
 :aggstar true 
 :aggdistinct false 
 }
  }
   )

   :qpqual <> 
   :lefttree 
  { SUBQUERYSCAN 
  :startup_cost 183.34 
  :total_cost 183.36 
  :rows 1 
  :width 22 
  :qptargetlist <> 
  :qpqual <> 
  :lefttree <> 
  :righttree <> 
  :extprm ()
   
  :locprm ()
   
  :initplan <> 
  :nprm 0  
  :scanrelid 1 
  :subplan 
 { AGG 
 :startup_cost 183.34 
 :total_cost 183.36 
 :rows 1 
 :width 22 
 :qptargetlist (
{ TARGETENTRY 
:resdom 
   { RESDOM 
   :resno 1 
   :restype 17 
   :restypmod -1 
   :resname tgargs 
   :reskey 0 
   :reskeyop 0 
   :ressortgroupref 1 
   :resjunk false 
   }
 
:expr 
   { VAR 
   :varno 0 
   :varattno 1 
   :vartype 17 
   :vartypmod -1  
   :varlevelsup 0 
   :varnoold 1 
   :varoattno 13
   }
}
 )
  
 :qpqual (
{ EXPR 
:typeOid 16  
:opType op 
:oper 
   { OPER 
   :opno 416 
   :opid 474 
   :opresulttype 16 
   :opretset false 
   }
 
:args (
   { AGGREG 
   :aggfnoid 2147 
   :aggtype 20 
   :target 
  { CONST 
  :consttype 23 
  :constlen 4 
  :constbyval true 
  :constisnull false 
  :constvalue  4 [ 0 0 0 1 ] 
  }

   :aggstar false 
   :aggdistinct false 
   }
 
   { CONST 
   :consttype 23 
   :constlen 4 
   :constbyval true 
   :constisnull false 
   :constvalue  4 [ 0 0 0 3 ] 
   }
)
}
 )
  
 :lefttree 
{ GRP 
:startup_cost 183.34 
:total_cost 183.35 
:rows 2 
:width 22 
:qptargetlist (
   { TARGETENTRY 
   :resdom