Re: [HACKERS] please help on query

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