Received: from mps4.plala.or.jp (mpsb-nat02.plala.or.jp [202.212.114.145])
	by postgresql.org (Postfix) with ESMTP id 7F78647585D
	for <pgsql-sql@postgresql.org>; Wed, 17 Jul 2002 09:27:51 -0400 (EDT)
Received: from [192.168.0.4] ([218.47.225.244]) by mps4.plala.or.jp with ESMTP
	id <20020717132753.STY18760.mps4.plala.or.jp@[192.168.0.4]>;
	Wed, 17 Jul 2002 22:27:53 +0900
Date: Wed, 17 Jul 2002 22:28:37 +0900
From: Masaru Sugawara <rk73@sea.plala.or.jp>
To: "Luis Alberto Amigo Navarro" <lamigo@atc.unican.es>
Subject: Re: [HACKERS] please help on query
Cc: pgsql-sql@postgresql.org
In-Reply-To: <00fc01c22ca5$ea5c78f0$cab990c1@atc.unican.es>
References: <20020716011524.3F70.RK73@sea.plala.or.jp>
	<00fc01c22ca5$ea5c78f0$cab990c1@atc.unican.es>
Errors-To: rk73@sea.plala.or.jp
Message-Id: <20020717215801.79DF.RK73@sea.plala.or.jp>
MIME-Version: 1.0
Content-Type: text/plain; charset="US-ASCII"
Content-Transfer-Encoding: 7bit
X-Mailer: Becky! ver. 2.05.03
X-Archive-Number: 200207/266
X-Sequence-Number: 8644

On Tue, 16 Jul 2002 10:51:03 +0200
"Luis Alberto Amigo Navarro" <lamigo@atc.unican.es> wrote:


> 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


Hmm, does each of the three tables have some indices like the following?
If not so, could you execute EXPLAIN ANALYZE after creating the indices.


create index idx_lineitem_orderkey on lineitem(orderkey);
create index idx_orders_orderkey on orders(orderkey);
create index idx_orders_custkey on orders(custkey);
create index idx_customer_custkey on customer(custkey);


Regards,
Masaru Sugawara