Query performanc issue - too many table? - Mailing list pgsql-admin

I am having a problem with the below SQL taking an extreme amount of time
to run.  The problem is that the explain looks great with all index scans.
But the query itself takes minutes to run.  The query contains 11 tables.
We've found that by dropping any one table, performance reverts to being
nearly instantaneous.

Here is my SQL:

SELECT ..... (20 or so simple columns from all tables)
FROM
    trip TRP,
    power_unit PWU,
    driver DRI,
    trailer TRL,
    trip_stop STP,
    transloading TXL,
    freight_group FGP,
    customer_order ORD,
    place PLC1,
    geo_location LOC1,
    place PLC2
WHERE
    TRP.trp_uid = '51972' AND
    TRP.pwu_uid=PWU.pwu_uid AND
    TRP.main_dri_uid=DRI.dri_uid AND
    TRP.trl_uid=TRL.trl_uid AND
    TRP.trp_uid=STP.trp_uid AND
    STP.stp_uid=TXL.stp_uid AND
    TXL.fgp_uid=FGP.fgp_uid AND
    FGP.ord_uid=ORD.ord_uid AND
    FGP.ship_plc_uid = PLC1.plc_uid AND
    PLC1.loc_uid = LOC1.loc_uid AND
    FGP.cons_plc_uid = PLC2.plc_uid;

Here is the EXPLAIN:

Nested Loop  (cost=0.00..1404.92 rows=8 width=552)
  ->  Nested Loop  (cost=0.00..1381.54 rows=8 width=548)
        ->  Nested Loop  (cost=0.00..1351.34 rows=8 width=490)
              ->  Nested Loop  (cost=0.00..1322.79 rows=8 width=486)
                    ->  Nested Loop  (cost=0.00..1292.60 rows=8 width=424)
                          ->  Nested Loop  (cost=0.00..1256.01 rows=12
width=342)
                                ->  Nested Loop  (cost=0.00..18.06 rows=5
width=314)
                                      ->  Nested Loop  (cost=0.00..14.74
rows=1 width=306)
                                            ->  Nested Loop
(cost=0.00..10.76 rows=1 width=248)
                                                  ->  Nested Loop
(cost=0.00..6.87 rows=1 width=190)
                                                        ->  Index Scan
using trip_pkey on trip trp cost=0.00..3.02 rows=1 width=24)
                                                        ->  Index Scan
using driver_pkey on driver dri  (cost=0.00..3.84 rows=1 width=166)
                                                  ->  Index Scan using
power_unit_pkey on power_unit pwu  (cost=0.00..3.88 rows=1 width=58)
                                            ->  Index Scan using
trailer_pkey on trailer trl  (cost=0.00..3.97 rows=1 width=58)
                                      ->  Index Scan using stp_trp_uid on
trip_stop stp  (cost=0.00..3.17 rows=12 width=8)
                                ->  Index Scan using txl_stp_uid on
transloading txl  (cost=0.00..253.05 rows=296 width=28)
                          ->  Index Scan using freight_group_pkey on
freight_group fgp  (cost=0.00..3.01 rows=1 width=82)
                    ->  Index Scan using place_pkey on place plc1
(cost=0.00..3.90 rows=1 width=62)
              ->  Index Scan using geo_location_pkey on geo_location loc1
(cost=0.00..3.68 rows=1 width=4)
        ->  Index Scan using place_pkey on place plc2  (cost=0.00..3.90
rows=1 width=58)
  ->  Index Scan using customer_order_pkey on customer_order ord
(cost=0.00..3.01 rows=1 width=4)

Explain shows rows but just to state, a few tables have ~300,000 rows and
the rest are well under 100,000.

we are running:
PostgreSQL 7.2.1 on i686-pc-linux-gnu, compiled by GCC 2.96

And we do a 'vacuum analyze' nightly.

Postmaster log does indicate Edge Combination Crossover but nothing else.

To us, the key issue is that eliminating any one table to bring total size
of query down to 10 tables makes things run fine.  At 11 tables, Explain
would seem to indicate things are still good but actual query time is
really bad.  Any help would be much appreciated.

Marc Mitchell - Senior Application Architect
Enterprise Information Solutions, Inc.
Downers Grove, IL 60515
marcm@eisolution.com