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

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@eisolution.com
>