join over 12 tables takes 3 secs to plan - Mailing list pgsql-performance

I have a query generated by an application (not mine, but there's
nothing I can find that looks bad about the query itself) that takes an
excessive amount of time to return even though there are almost no rows
in the schema yet. 3 secs may not seem to be much, but the query is run
by a web-application for a page you have to go through quite
frequently, and it appears the query should be able to execute below 1
sec easily. I'm running Postgres 7.3.1 on Mac OSX.

After having turned on several logging options, here is a pertinent
excerpt from the log that also shows the query. It seems the query
planner takes the whole time, not the actual execution. Does anyone
have an idea what's going on here, and what I could do to alleviate the
problem? (Just to mention, I've run the same with GEQO off and if
anything it makes the timing worse.)

2003-01-02 11:22:59 LOG:  query: SELECT TW.WORKITEMKEY,
TW.PACKAGESYNOPSYS, TW.PACKAGEDESCRIPTION, TW.BUILD,
TW.LASTEDIT, TOW.LASTNAME AS LOWNER, TOW.FIRSTNAME AS FOWNER,
TOR.LASTNAME AS LORIGINATOR, TOR.FIRSTNAME AS FORIGINATOR,
TRE.LASTNAME AS LRESPONSIBLE, TRE.FIRSTNAME AS FRESPONSIBLE,
TPRJC.LABEL AS PROJCATLABEL, TPRJ.LABEL AS PROJLABEL, TCL.LABEL AS
REQCLASS,
TW.CATEGORYKEY AS REQCATEGORY, TW.PRIORITYKEY AS REQPRIORITY,
TW.SEVERITYKEY AS REQSEVERITY, TST.LABEL AS STATELABEL,  TW.STATE,
TST.STATEFLAG, TREL.LABEL AS RELEASELABEL, TW.ENDDATE
FROM TWORKITEM TW, TPERSON TOW, TPERSON TOR, TPERSON TRE, TPROJECT TPRJ,
TPROJCAT TPRJC, TCATEGORY TCAT, TCLASS TCL, TPRIORITY TPRIO, TSEVERITY
TSEV,
TSTATE TST, TRELEASE TREL
WHERE (TW.OWNER = TOW.PKEY) AND (TW.ORIGINATOR = TOR.PKEY)
AND (TW.RESPONSIBLE = TRE.PKEY) AND (TW.PROJCATKEY = TPRJC.PKEY)
AND (TPRJ.PKEY = TPRJC.PROJKEY) AND (TW.CLASSKEY = TCL.PKEY)
AND (TW.CATEGORYKEY = TCAT.PKEY) AND (TW.PRIORITYKEY = TPRIO.PKEY)
AND (TW.SEVERITYKEY = TSEV.PKEY) AND (TST.PKEY = TW.STATE)
AND (TREL.PKEY = TW.RELSCHEDULEDKEY)

2003-01-02 11:23:02 LOG:  PLANNER STATISTICS
! system usage stats:
!       2.730501 elapsed 1.400000 user 0.000000 system sec
!       [3.580000 user 0.000000 sys total]
!       0/0 [0/0] filesystem blocks in/out
!       0/0 [0/0] page faults/reclaims, 0 [0] swaps
!       0 [0] signals rcvd, 0/0 [0/14] messages rcvd/sent
!       0/0 [24/0] voluntary/involuntary context switches
! buffer usage stats:
!       Shared blocks:          0 read,          0 written, buffer hit
rate = 0.00%
!       Local  blocks:          0 read,          0 written, buffer hit
rate = 0.00%
!       Direct blocks:          0 read,          0 written
2003-01-02 11:23:02 LOG:  EXECUTOR STATISTICS
! system usage stats:
!       0.005024 elapsed 0.000000 user 0.000000 system sec
!       [3.580000 user 0.000000 sys total]
!       0/0 [0/0] filesystem blocks in/out
!       0/0 [0/0] page faults/reclaims, 0 [0] swaps
!       0 [0] signals rcvd, 0/0 [0/14] messages rcvd/sent
!       0/0 [24/0] voluntary/involuntary context switches
! buffer usage stats:
!       Shared blocks:          0 read,          0 written, buffer hit
rate = 100.00%
!       Local  blocks:          0 read,          0 written, buffer hit
rate = 0.00%
!       Direct blocks:          0 read,          0 written
2003-01-02 11:23:02 LOG:  duration: 2.740243 sec
2003-01-02 11:23:02 LOG:  QUERY STATISTICS
! system usage stats:
!       0.006432 elapsed 0.000000 user 0.000000 system sec
!       [3.580000 user 0.000000 sys total]
!       0/0 [0/0] filesystem blocks in/out
!       0/0 [0/0] page faults/reclaims, 0 [0] swaps
!       0 [0] signals rcvd, 0/0 [0/14] messages rcvd/sent
!       0/0 [24/0] voluntary/involuntary context switches
! buffer usage stats:
!       Shared blocks:          0 read,          0 written, buffer hit
rate = 100.00%
!       Local  blocks:          0 read,          0 written, buffer hit
rate = 0.00%
!       Direct blocks:          0 read,          0 written

--
-------------------------------------------------------------
Hilmar Lapp                            email: lapp at gnf.org
GNF, San Diego, Ca. 92121              phone: +1-858-812-1757
-------------------------------------------------------------