Re: Query performanc issue - too many table?

This is really just a standard "can you post explain analyze output"
response, though I am wondering if might help you to use explicit joins
on some of these tables.

Robert Treat

On Thu, 2002-11-21 at 11:59, Marc Mitchell wrote:
> 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(at)eisolution(dot)com
>