Re: TPC-R benchmarks

All right, my query just finished running with EXPLAIN ANALYZE.
I show the plan below and also attached it as a file.
Any ideas?

   ->  Sort  (cost=54597.49..54597.50 rows=1 width=121) (actual
time=6674562.03..6674562.15 rows=175 loops=1)
         Sort Key: nation.n_name, date_part('year'::text,
orders.o_orderdate)
         ->  Aggregate  (cost=54597.45..54597.48 rows=1 width=121)
(actual time=6668919.41..6674522.48 rows=175 loops=1)
               ->  Group  (cost=54597.45..54597.47 rows=3 width=121)
(actual time=6668872.68..6672136.96 rows=348760 loops=1)
                     ->  Sort  (cost=54597.45..54597.46 rows=3
width=121) (actual time=6668872.65..6669499.95 rows=348760 loops=1)
                           Sort Key: nation.n_name,
date_part('year'::text, orders.o_orderdate)
                           ->  Hash Join  (cost=54596.00..54597.42
rows=3
width=121) (actual time=6632768.89..6650192.67 rows=348760 loops=1)
                                 Hash Cond: ("outer".n_nationkey =
"inner".s_nationkey)
                                 ->  Seq Scan on nation
(cost=0.00..1.25 rows=25 width=33) (actual time=6.75..7.13 rows=25
loops=1)
                                 ->  Hash  (cost=54596.00..54596.00
rows=3
width=88) (actual time=6632671.96..6632671.96 rows=0 loops=1)
                                       ->  Nested Loop
(cost=0.00..54596.00 rows=3 width=88) (actual time=482.41..6630601.46
rows=348760 loops=1)
                                             Join Filter:
("inner".s_suppkey = "outer".l_suppkey)
                                             ->  Nested Loop
(cost=0.00..54586.18 rows=3 width=80) (actual time=383.87..6594984.40
rows=348760 loops=1)
                                                   ->  Nested Loop
(cost=0.00..54575.47 rows=4 width=68) (actual time=199.95..3580882.07
rows=348760 loops=1)
                                                         Join Filter:
("outer".p_partkey = "inner".ps_partkey)
                                                         ->  Nested Loop
(cost=0.00..22753.33 rows=9343 width=49) (actual time=146.85..3541433.10
rows=348760 loops=1)
                                                               ->  Seq
Scan on part  (cost=0.00..7868.00 rows=320 width=4) (actual
time=33.64..15651.90 rows=11637 loops=1)

Filter: (p_name ~~ '%green%'::text)
                                                               ->  Index
Scan using i_l_partkey on lineitem  (cost=0.00..46.15 rows=29 width=45)
(actual time=10.71..302.67 rows=30 loops=11637)

Index
Cond: ("outer".p_partkey = lineitem.l_partkey)
                                                         ->  Index Scan
using pk_partsupp on partsupp  (cost=0.00..3.39 rows=1 width=19) (actual
time=0.09..0.09 rows=1 loops=348760)
                                                               Index
Cond: ((partsupp.ps_partkey = "outer".l_partkey) AND
(partsupp.ps_suppkey =
"outer".l_suppkey))
                                                   ->  Index Scan using
pk_orders on orders  (cost=0.00..3.01 rows=1 width=12) (actual
time=8.62..8.62 rows=1 loops=348760)
                                                         Index Cond:
(orders.o_orderkey = "outer".l_orderkey)
                                             ->  Index Scan using
pk_supplier on supplier  (cost=0.00..3.01 rows=1 width=8) (actual
time=0.08..0.08 rows=1 loops=348760)
                                                   Index Cond:
("outer".ps_suppkey = supplier.s_suppkey)  Total runtime: 6674724.23
msec (28 rows)


-----Original Message-----
From: Oleg Lebedev
Sent: Wednesday, October 01, 2003 12:00 PM
To: Josh Berkus; scott.marlowe
Cc: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] TPC-R benchmarks
Importance: Low


Sure, below is the query. I attached the plan to this posting.

select
    nation,
    o_year,
    sum(amount) as sum_profit
from
    (
        select
            n_name as nation,
            extract(year from o_orderdate) as o_year,
            l_extendedprice * (1 - l_discount) -
ps_supplycost * l_quantity as amount
        from
            part,
            supplier,
            lineitem,
            partsupp,
            orders,
            nation
        where
            s_suppkey = l_suppkey
            and ps_suppkey = l_suppkey
            and ps_partkey = l_partkey
            and p_partkey = l_partkey
            and o_orderkey = l_orderkey
            and s_nationkey = n_nationkey
            and p_name like '%green%'
    ) as profit
group by
    nation,
    o_year
order by
    nation,
    o_year desc;


-----Original Message-----
From: Josh Berkus [mailto:josh@agliodbs.com]
Sent: Wednesday, October 01, 2003 11:42 AM
To: Oleg Lebedev; scott.marlowe
Cc: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] TPC-R benchmarks


Oleg,

> The output of the query should contain about 200 rows. So, I guess the

> planer is off assuming that the query should return 1 row.

Oh, also did you post the query before?   Can you re-post it with the
planner
results?

--
Josh Berkus
Aglio Database Solutions
San Francisco

*************************************

This e-mail may contain privileged or confidential material intended for
the named recipient only. If you are not the named recipient, delete
this message and all attachments. Unauthorized reviewing, copying,
printing, disclosing, or otherwise using information in this e-mail is
prohibited. We reserve the right to monitor e-mail sent through our
network.

*************************************

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
    (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

*************************************

This e-mail may contain privileged or confidential material intended for the named recipient only.
If you are not the named recipient, delete this message and all attachments.
Unauthorized reviewing, copying, printing, disclosing, or otherwise using information in this e-mail is prohibited.
We reserve the right to monitor e-mail sent through our network.

*************************************