Re: [HACKERS] please help on query

----- Original Message -----
From: "Masaru Sugawara" <rk73(at)sea(dot)plala(dot)or(dot)jp>
To: "Luis Alberto Amigo Navarro" <lamigo(at)atc(dot)unican(dot)es>
Cc: <pgsql-sql(at)postgresql(dot)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.00 rows=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=600122 width=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.04 rows=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