Re: [HACKERS] please help on query

On Tue, 16 Jul 2002 10:51:03 +0200
"Luis Alberto Amigo Navarro" <lamigo(at)atc(dot)unican(dot)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