pg.ddx.io pgsql-sql@postgresql.org mailing list archive help / color / mirror / Atom feed From: Luis Alberto Amigo Navarro <lamigo@atc.unican.es>
To: Masaru Sugawara <rk73@sea.plala.or.jp>
Cc: pgsql-sql@postgresql.org
Subject: Re: [HACKERS] please help on query
Date: Tue, 16 Jul 2002 10:51:03 +0200
Message-ID: <00fc01c22ca5$ea5c78f0$cab990c1@atc.unican.es> (raw )
References: <20020714211624.9C3E.RK73@sea.plala.or.jp >
<005101c22bd3$9ba152d0$cab990c1@atc.unican.es >
<20020716011524.3F70.RK73@sea.plala.or.jp >
----- 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.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
view thread (18+ messages) latest in thread
Message-ID: <00fc01c22ca5$ea5c78f0$cab990c1@atc.unican.es>
Permalink: ../00fc01c22ca5$ea5c78f0$cab990c1@atc.unican.es/
Also on: postgresql.org/message-id/00fc01c22ca5$ea5c78f0$cab990c1@atc.unican.es
copy link · copy postgr.es
reply Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: lamigo@atc.unican.es, rk73@sea.plala.or.jp
Subject: Re: [HACKERS] please help on query
In-Reply-To: <00fc01c22ca5$ea5c78f0$cab990c1@atc.unican.es>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox