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:[EMAIL PROTECTED] Sent: Wednesday, October 01, 2003 11:42 AM To: Oleg Lebedev; scott.marlowe Cc: [EMAIL PROTECTED] 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. *************************************
-> Sort (cost=54597.49..54597.50 rows=1 width=121) Sort Key: nation.n_name, date_part('year'::text,orders.o_orderdate) -> Aggregate (cost=54597.45..54597.48 rows=1 width=121) -> Group (cost=54597.45..54597.47 rows=3 width=121) -> Sort (cost=54597.45..54597.46 rows=3 width=121) Sort Key: nation.n_name, date_part('year'::text, orders.o_orderdate) -> Hash Join (cost=54596.00..54597.42 rows=3 width=121) Hash Cond: ("outer".n_nationkey = "inner".s_nationkey) -> Seq Scan on nation (cost=0.00..1.25 rows=25 width=33) -> Hash (cost=54596.00..54596.00 rows=3 width=88) -> Nested Loop (cost=0.00..54596.00 rows=3 width=88) Join Filter: ("inner".s_suppkey = "outer".l_suppkey) -> Nested Loop (cost=0.00..54586.18 rows=3 width=80) -> Nested Loop (cost=0.00..54575.47 rows=4 width=68) Join Filter: ("outer".p_partkey = "inner".ps_partkey) -> Nested Loop (cost=0.00..22753.33 rows=9343 width=49) -> Seq Scan on part (cost=0.00..7868.00 rows=320 width=4) Filter: (p_name ~~ '%green%'::text) -> Index Scan using i_l_partkey on lineitem (cost=0.00..46.15 rows=29 width=45) Index Cond: ("outer".p_partkey = lineitem.l_partkey) -> Index Scan using pk_partsupp on partsupp (cost=0.00..3.39 rows=1 width=19) 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) Index Cond: (orders.o_orderkey = "outer".l_orderkey) -> Index Scan using pk_supplier on supplier (cost=0.00..3.01 rows=1 width=8) Index Cond: ("outer".ps_suppkey = supplier.s_suppkey) (27 rows)
---------------------------(end of broadcast)--------------------------- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])