Re: [HACKERS] please help on query - Mailing list pgsql-sql

----- Original Message -----
From: "Masaru Sugawara" <rk73@sea.plala.or.jp>
To: "Luis Alberto Amigo Navarro" <lamigo@atc.unican.es>
Cc: <pgsql-sql@postgresql.org>
Sent: Monday, July 15, 2002 6:15 PM
Subject: Re: [SQL] [HACKERS] please help on query



>
> Sorry, I don't know the reason.
> I need more info. Can you show me the outputs of EXPLAIN ANALYZE ?
>
Here it is:


>
> EXPLAIN ANALYZE
> SELECT
>         orders.orderkey
>     FROM
>         lineitem LEFT OUTER JOIN
>         orders USING(orderkey)
>     WHERE
>         orders.orderkey IS NOT NULL
>     GROUP BY
>         orders.orderkey
>     HAVING
>         SUM(lineitem.quantity) > 300;
>
Aggregate  (cost=1257368.92..1287375.04 rows=600122 width=12) (actual
time=1236941.71..1454824.56 rows=62 loops=1) ->  Group  (cost=1257368.92..1272371.98 rows=6001225 width=12) (actual
time=1233968.87..1385034.91 rows=6001225 loops=1)       ->  Sort  (cost=1257368.92..1257368.92 rows=6001225 width=12)
(actual time=1233968.82..1276147.37 rows=6001225 loops=1)             ->  Hash Join  (cost=166395.00..520604.08
rows=6001225
width=12) (actual time=59061.21..773997.08 rows=6001225 loops=1)                   ->  Seq Scan on lineitem
(cost=0.00..195405.25
rows=6001225 width=8) (actual time=20.66..115511.34 rows=6001225 loops=1)                   ->  Hash
(cost=162645.00..162645.00rows=1500000
 
width=4) (actual time=59032.16..59032.16 rows=0 loops=1)                         ->  Seq Scan on orders
(cost=0.00..162645.00
rows=1500000 width=4) (actual time=17.33..44420.10 rows=1500000 loops=1)
Total runtime: 1454929.11 msec




>
>
> EXPLAIN ANALYZE
> SELECT
>       t2.*
> FROM (SELECT
>                   orders.orderkey
>               FROM
>                    lineitem LEFT OUTER JOIN
>                    orders USING(orderkey)
>              WHERE
>                    orders.orderkey IS NOT NULL
>             GROUP BY
>                    orders.orderkey
>             HAVING
>                     SUM(lineitem.quantity) > 300
>            ) AS t1 LEFT OUTER JOIN
>            orders AS t2 USING(orderkey)
> ORDER BY t2.custkey
>

Sort  (cost=1739666.43..1739666.43 rows=600122 width=119) (actual
time=1538897.23..1538897.47 rows=62 loops=1) ->  Merge Join  (cost=1344971.49..1682069.98 rows=600122 width=119)
(actual time=1440886.58..1538886.03 rows=62 loops=1)       ->  Index Scan using orders_pkey on orders t2
(cost=0.00..324346.65
rows=1500000 width=115) (actual time=32.80..87906.98 rows=1455276 loops=1)       ->  Sort  (cost=1344971.49..1344971.49
rows=600122width=12) (actual
 
time=1439550.31..1439550.73 rows=62 loops=1)             ->  Subquery Scan t1  (cost=1257368.92..1287375.04
rows=600122
width=12) (actual time=1222560.86..1439549.36 rows=62 loops=1)                   ->  Aggregate
(cost=1257368.92..1287375.04rows=600122
 
width=12) (actual time=1222560.84..1439548.42 rows=62 loops=1)                         ->  Group
(cost=1257368.92..1272371.98
rows=6001225 width=12) (actual time=1219607.04..1369327.42 rows=6001225
loops=1)                               ->  Sort  (cost=1257368.92..1257368.92
rows=6001225 width=12) (actual time=1219607.00..1261208.08 rows=6001225
loops=1)                                     ->  Hash Join
(cost=166395.00..520604.08 rows=6001225 width=12) (actual
time=65973.31..769253.41 rows=6001225 loops=1)                                           ->  Seq Scan on lineitem
(cost=0.00..195405.25 rows=6001225 width=8) (actual time=20.07..115247.61
rows=6001225 loops=1)                                           ->  Hash
(cost=162645.00..162645.00 rows=1500000 width=4) (actual
time=65943.80..65943.80 rows=0 loops=1)                                                 ->  Seq Scan on orders
(cost=0.00..162645.00 rows=1500000 width=4) (actual time=39.04..52049.90
rows=1500000 loops=1)
Total runtime: 1539010.00 msec



Thanks and regards