Re: [PERFORM] TPC-R benchmarks
Josh, My data directory is 3.8 GB. I can send you flat data files and scripts to create indices, but still it would be about 1.3 GB of data. Do you still want me to transfer data to you? If yes, then just give me your FTP address. Thanks. Oleg -Original Message- From: Josh Berkus [mailto:[EMAIL PROTECTED] Sent: Friday, October 03, 2003 11:22 AM To: Oleg Lebedev; scott.marlowe Cc: [EMAIL PROTECTED] Subject: Re: [PERFORM] TPC-R benchmarks Oleg, > I declared all the indexes that you suggested and ran vacuum full > analyze. The query plan has not changed and it's still trying to use > seqscan. I tried to disable seqscan, but the plan didn't change. Any > other suggestions? I started explain analyze on the query, but I doubt > it will finish any time soon. Can I get a copy of the database so that I can tinker? I'm curious now, plus I want our benchmarks to look good. I have a private FTP if that helps. -- 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 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] TPC-R benchmarks
Josh, I declared all the indexes that you suggested and ran vacuum full analyze. The query plan has not changed and it's still trying to use seqscan. I tried to disable seqscan, but the plan didn't change. Any other suggestions? I started explain analyze on the query, but I doubt it will finish any time soon. Thanks. Oleg -Original Message- From: Josh Berkus [mailto:[EMAIL PROTECTED] Sent: Thursday, October 02, 2003 11:27 PM To: Oleg Lebedev; scott.marlowe Cc: [EMAIL PROTECTED] Subject: Re: [PERFORM] TPC-R benchmarks Oleg, > I have another question. How do I optimize my indexes for the query > that contains a lot of ORed blocks, each of which contains a bunch of > ANDed expressions? The structure of each ORed block is the same except > the right-hand-side values vary. Given the example, I'd do a multicolumn index on p_brand, p_container, p_size and a second multicolumn index on l_partkey, l_quantity, l_shipmode. Hmmm ... or maybe seperate indexes, one on l_partkey and one on l_quantity, l_shipmode & l_instruct. Test both configurations. Mind you, if this is also an OLTP table, then you'd want to test those multi-column indexes to determine the least columns you need for the indexes still to be used, since more columns = more index maintainence. -- 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 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PERFORM] TPC-R benchmarks
Thanks everyone for the help.
I have another question. How do I optimize my indexes for the query that
contains a lot of ORed blocks, each of which contains a bunch of ANDed
expressions? The structure of each ORed block is the same except the
right-hand-side values vary.
The first expression of each AND-block is a join condition. However,
postgres tries to use a sequential scan on both of the tables applying
the OR-ed blocks of ANDed expressions. So, the cost of the plan is
around 700,000,000,000.
Here is an example:
select
sum(l_extendedprice* (1 - l_discount)) as revenue
from
lineitem,
part
where
(
p_partkey = l_partkey
and p_brand = 'Brand#24'
and p_container in ('SM CASE', 'SM BOX', 'SM PACK', 'SM
PKG')
and l_quantity >= 4 and l_quantity <= 4 + 10
and p_size between 1 and 5
and l_shipmode in ('AIR', 'AIR REG')
and l_shipinstruct = 'DELIVER IN PERSON'
)
or
(
p_partkey = l_partkey
and p_brand = 'Brand#22'
and p_container in ('MED BAG', 'MED BOX', 'MED PKG',
'MED PACK')
and l_quantity >= 18 and l_quantity <= 18 + 10
and p_size between 1 and 10
and l_shipmode in ('AIR', 'AIR REG')
and l_shipinstruct = 'DELIVER IN PERSON'
)
or
(
p_partkey = l_partkey
and p_brand = 'Brand#33'
and p_container in ('LG CASE', 'LG BOX', 'LG PACK', 'LG
PKG')
and l_quantity >= 24 and l_quantity <= 24 + 10
and p_size between 1 and 15
and l_shipmode in ('AIR', 'AIR REG')
and l_shipinstruct = 'DELIVER IN PERSON'
);
-Original Message-
From: scott.marlowe [mailto:[EMAIL PROTECTED]
Sent: Thursday, October 02, 2003 1:44 PM
To: Oleg Lebedev
Cc: Josh Berkus; [EMAIL PROTECTED]
Subject: RE: [PERFORM] TPC-R benchmarks
On Thu, 2 Oct 2003, Oleg Lebedev wrote:
> I was trying to get the pg_stats information to Josh and decided to
> recreate the indexes on all my tables. After that I ran vacuum full
> analyze, re-enabled nestloop and ran explain analyze on the query. It
> ran in about 2 minutes. I attached the new query plan. I am not sure
> what did the trick, but 2 minutes is much better than 2 hours. But
> then again, I can't take long lunches anymore :)
> Is there any way to make this query run even faster without increasing
> the memory dedicated to postgres?
> Thanks.
As long as the estimated row counts and real ones match up, and
postgresql
seems to be picking the right plan, there's probably not a lot to be
done.
You might want to look at increasing sort_mem a bit, but don't go crazy,
as being too high can result in swap storms under load, which are a very
bad thing.
I'd check for index growth. You may have been reloading your data over
and over and had an index growth problem. Next time instead of
recreating
the indexed completely, you might wanna try reindex indexname.
Also, 7.4 mostly fixes the index growth issue, especially as it applies
to
truncating/reloading a table over and over, so moving to 7.4 beta3/4 and
testing might be a good idea (if you aren't there already).
What you want to avoid is having postgresql switch back to that nestloop
join on you in the middle of the day, and to prevent that you might need
to have higher statistics targets so the planner gets the right number
all the time.
*
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 8: explain analyze is your friend
Re: [PERFORM] TPC-R benchmarks
I was trying to get the pg_stats information to Josh and decided to
recreate the indexes on all my tables. After that I ran vacuum full
analyze, re-enabled nestloop and ran explain analyze on the query. It
ran in about 2 minutes.
I attached the new query plan. I am not sure what did the trick, but 2
minutes is much better than 2 hours. But then again, I can't take long
lunches anymore :)
Is there any way to make this query run even faster without increasing
the memory dedicated to postgres?
Thanks.
Oleg
-Original Message-
From: scott.marlowe [mailto:[EMAIL PROTECTED]
Sent: Thursday, October 02, 2003 10:29 AM
To: Oleg Lebedev
Cc: Josh Berkus; [EMAIL PROTECTED]
Subject: RE: [PERFORM] TPC-R benchmarks
Have you tried increasing the statistics target for those columns that
are
getting bad estimates yet and then turning back on enable_nestloop and
rerunning analyze and seeing how the query does?
The idea being to try and get a good enough estimate of your statistics
so
the planner stops using nestloops on its own rather than forcing it to
with enable_nestloop = false.
On Thu, 2 Oct 2003, Oleg Lebedev wrote:
> As Scott recommended, I did the following:
> # set enable_nestloop = false;
> # vacuum full analyze;
>
> After this I re-ran the query and its execution time went down from 2
> hours to 2 minutes. I attached the new query plan to this posting. Is
> there any way to optimize it even further? What should I do to make
> this query run fast without hurting the performance of the other
> queries? Thanks.
>
> Oleg
>
> -Original Message-
> From: scott.marlowe [mailto:[EMAIL PROTECTED]
> Sent: Wednesday, October 01, 2003 4:00 PM
> To: Oleg Lebedev
> Cc: Josh Berkus; [EMAIL PROTECTED]
> Subject: Re: [PERFORM] TPC-R benchmarks
>
>
> For troubleshooting, can you try it with "set enable_nestloop = false"
> and rerun the query and see how long it takes?
>
> It looks like the estimates of rows returned is WAY off (estimate is
> too
>
> low compared to what really comes back.)
>
> Also, you might try to alter the table.column to have a higher target
> on
>
> the rows p_partkey and ps_partkey and any others where the estimate is
> so far off of the reality.
>
> On Wed, 1 Oct 2003, Oleg Lebedev wrote:
>
> > 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..22
Re: [PERFORM] TPC-R benchmarks
As Scott recommended, I did the following:
# set enable_nestloop = false;
# vacuum full analyze;
After this I re-ran the query and its execution time went down from 2
hours to 2 minutes. I attached the new query plan to this posting.
Is there any way to optimize it even further?
What should I do to make this query run fast without hurting the
performance of the other queries?
Thanks.
Oleg
-Original Message-
From: scott.marlowe [mailto:[EMAIL PROTECTED]
Sent: Wednesday, October 01, 2003 4:00 PM
To: Oleg Lebedev
Cc: Josh Berkus; [EMAIL PROTECTED]
Subject: Re: [PERFORM] TPC-R benchmarks
For troubleshooting, can you try it with "set enable_nestloop = false"
and
rerun the query and see how long it takes?
It looks like the estimates of rows returned is WAY off (estimate is too
low compared to what really comes back.)
Also, you might try to alter the table.column to have a higher target on
the rows p_partkey and ps_partkey and any others where the estimate is
so
far off of the reality.
On Wed, 1 Oct 2003, Oleg Lebedev wrote:
> 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)
>
Re: [PERFORM] TPC-R benchmarks
I ran VACUUM FULL ANALYZE yesterday and the re-ran the query with EXPLAIN ANALYZE. I got the same query plan and execution time. -Original Message- From: Tom Lane [mailto:[EMAIL PROTECTED] Sent: Wednesday, October 01, 2003 4:20 PM To: Oleg Lebedev Cc: Josh Berkus; scott.marlowe; [EMAIL PROTECTED] Subject: Re: [PERFORM] TPC-R benchmarks Oleg Lebedev <[EMAIL PROTECTED]> writes: > All right, my query just finished running with EXPLAIN ANALYZE. I show > the plan below and also attached it as a file. Any ideas? Uh, have you done an ANALYZE (or VACUUM ANALYZE) on this database? It sure looks like the planner thinks the tables are a couple of orders of magnitude smaller than they actually are. Certainly the estimated sizes of the joins are way off :-( If you did analyze, it might help to increase the statistics target and re-analyze. regards, tom lane * 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 [EMAIL PROTECTED])
Re: [PERFORM] 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: [EMAIL PROTECTED]
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,
Re: [PERFORM] TPC-R benchmarks
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])
Re: [PERFORM] Tuning/performance issue...
That would be great! When do you think this would be ready for us to see ;?) -Original Message- From: Jeff [mailto:[EMAIL PROTECTED] Sent: Wednesday, October 01, 2003 11:42 AM To: Oleg Lebedev Cc: [EMAIL PROTECTED] Subject: RE: [PERFORM] Tuning/performance issue... On Wed, 1 Oct 2003, Oleg Lebedev wrote: > Jeff, > I would really appreciate if you could send me that lengthy > presentation that you've written on pg/other dbs comparison. Thanks. > After I give the presentation at work and collect comments from my coworkers (and remove some information you folks don't need to know :) I will be very willing to post it for people to see. -- Jeff Trout <[EMAIL PROTECTED]> http://www.jefftrout.com/ http://www.stuarthamm.net/ * 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 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PERFORM] TPC-R benchmarks
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. I will start EXPLAIN ANALYZE now. Thanks. Oleg -Original Message- From: scott.marlowe [mailto:[EMAIL PROTECTED] Sent: Wednesday, October 01, 2003 7:23 AM To: Oleg Lebedev Cc: [EMAIL PROTECTED] Subject: Re: [PERFORM] TPC-R benchmarks On Tue, 30 Sep 2003, Oleg Lebedev wrote: > I continue struggling with the TPC-R benchmarks and wonder if anyone > could help me optimize the query below. ANALYZE statistics indicate > that the query should run relatively fast, but it takes hours to > complete. I attached the query plan to this posting. Thanks. What are the differences between estimated and real rows and such of an explain analyze on that query? Are there any estimates that are just way off? * 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 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] Tuning/performance issue...
Jeff, I would really appreciate if you could send me that lengthy presentation that you've written on pg/other dbs comparison. Thanks. Oleg -Original Message- From: Jeff [mailto:[EMAIL PROTECTED] Sent: Wednesday, October 01, 2003 6:23 AM To: David Griffiths Cc: [EMAIL PROTECTED] Subject: Re: [PERFORM] Tuning/performance issue... Importance: Low On Tue, 30 Sep 2003, David Griffiths wrote: > > This is all part of a "migrate away from Oracle" project. We are > looking at 3 databases - MySQL (InnoDB), Postgres and Matisse (object > oriented). We have alot of queries like this > or worse, and I'm worried that many of them would need to be re-written. The > developers > know SQL, but nothing about tuning, etc. > There's a movement at my company to ditch several commercial db's in favor of a free one. I'm currently the big pg fan around here and I've actually written a rather lengthy presentation about pg features, why, tuning, etc. but another part was some comparisons to other db's.. I decided so I wouldn't be blinding flaming mysql to give it a whirl and loaded it up with the same dataset as pg. First thing I hit was lack of stored procedures. But I decided to code around that, giving mysql the benefit of the doubt. What I found was interesting. For 1-2 concurrent 'beaters' it screamed. ultra-fast. But.. If you increase the concurrent beaters up to say, 20 Mysql comes to a grinding halt.. Mysql and the machine itself become fairly unresponsive. And if you do cache unfriendly queries it becomes even worse. On PG - no problems at all. Scaled fine and dandy up. And with 40 concurrent beaters the machine was still responsive. (The numbers for 20 client was 220 seconds (pg) and 650 seconds (mysql)) So that is another test to try out - Given your configuration I expect you have lots of concurrent activity. -- Jeff Trout <[EMAIL PROTECTED]> http://www.jefftrout.com/ http://www.stuarthamm.net/ ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match * 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 8: explain analyze is your friend
Re: [PERFORM] TPC-R benchmarks
I continue struggling with the TPC-R benchmarks and wonder if anyone
could help me optimize the query below. ANALYZE statistics indicate that
the query should run relatively fast, but it takes hours to complete. I
attached the query plan to this posting.
Thanks.
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 '%aquamarine%'
) as profit
group by
nation,
o_year
order by
nation,
o_year desc;
*
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.
*
QUERY PLAN
Subquery Scan "temp" (cost=18237.40..18237.41 rows=1 width=121)
-> Sort (cost=18237.40..18237.41 rows=1 width=121)
Sort Key: nation.n_name, date_part('year'::text, orders.o_orderdate)
-> Aggregate (cost=18237.38..18237.39 rows=1 width=121)
-> Group (cost=18237.38..18237.39 rows=1 width=121)
-> Sort (cost=18237.38..18237.38 rows=1 width=121)
Sort Key: nation.n_name, date_part('year'::text, orders.o_orderdate)
-> Nested Loop (cost=18232.96..18237.37 rows=1 width=121)
-> Hash Join (cost=18232.96..18234.34 rows=1 width=109)
Hash Cond: ("outer".n_nationkey = "inner".s_nationkey)
-> Seq Scan on nation (cost=0.00..1.25 rows=25 width=33)
-> Hash (cost=18232.95..18232.95 rows=1 width=76)
-> Nested Loop (cost=0.00..18232.95 rows=1 width=76)
Join Filter: ("inner".s_suppkey = "outer".l_suppkey)
-> Nested Loop (cost=0.00..18229.93 rows=1 width=68)
Join Filter: ("outer".p_partkey = "inner".l_partkey)
-> Nested Loop (cost=0.00..8021.14 rows=4 width=23)
-> Seq Scan on part (cost=0.00..8018.00 rows=1 width=4)
Filter: (p_name ~~ '%aquamarine%'::text)
-> Index Scan using i_ps_partkey on partsupp (cost=0.00..3.07 rows=5 width=19)
Index Cond: ("outer".p_partkey = partsupp.ps_partkey)
-> Index Scan using i_l_suppkey on lineitem (cost=0.00..2542.69 rows=634 width=45)
Index Cond: ("outer".ps_suppkey = lineitem.l_suppkey)
-> Index Scan using pk_supplier on supplier (cost=0.00..3.01 rows=1 width=8)
Index Cond: ("outer".ps_suppkey = supplier.s_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)
(27 rows)
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [PERFORM] TPC-R benchmarks
Yes Josh, L_partkey is a part of the foreign key on the Lineitem table, and it was ok to create an index on it according to the TPC-R specs. I just created indices on the rest of the FK columns in the TPC-R database and will continue my evaluations. Thanks. Oleg -Original Message- From: Josh Berkus [mailto:[EMAIL PROTECTED] Sent: Monday, September 29, 2003 12:11 PM To: Oleg Lebedev; Mary Edie Meredith Cc: Jenny Zhang; pgsql-performance Subject: Re: [PERFORM] TPC-R benchmarks Oleg, > I just checked the restrictions on the TPC-R and TPC-H schemas and it > seems that all indexes are allowed in TPC-R and only those that index > parts of primary or foreign keys are allowed in TPC-H. That would be appropriate for this case though, yes? That column is part of a foriegn key, unless I've totally lost track. As I remarked before, Postgres does *not* automatically create indexes for FKs. Many, but not all, other database products do, so comparing PostgreSQL against those products without the index is unfair. -- 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 [EMAIL PROTECTED])
Re: [PERFORM] TPC-R benchmarks
Oops, my previous message got cut off. Here is the end of it: I just checked the restrictions on the TPC-R and TPC-H schemas and it seems that all indexes are allowed in TPC-R and only those that index parts of primary or foreign keys are allowed in TPC-H. Thanks. Oleg -Original Message- From: Oleg Lebedev Sent: Monday, September 29, 2003 11:23 AM To: Mary Edie Meredith Cc: Jenny Zhang; pgsql-performance Subject: Re: [PERFORM] TPC-R benchmarks Importance: Low It took 10 hours to compute the query without the index on lineitem.l_partkey. Once I created the index on lineitem.l_partkey, it took only 32 secs to run the same query. After VACUUM ANALYZE it took 72 secs to run the query. All the subsequent runs took under 3 seconds! That's quite amazing! I just checked -Original Message- From: Mary Edie Meredith [mailto:[EMAIL PROTECTED] Sent: Monday, September 29, 2003 10:04 AM To: Oleg Lebedev Cc: Tom Lane; Jenny Zhang; pgsql-performance Subject: RE: [PERFORM] TPC-R benchmarks On Mon, 2003-09-29 at 07:35, Oleg Lebedev wrote: > I left my TPC-R query #17 working over the weekend and it took 3988 > mins ~ 10 hours to complete. And this is considering that I am using a > TPC-R database created with a scale factor of 1, which corresponds to > ~1 GB of data. I am running RedHat 8.0 on a dual 1 GHz processor, 512 > MB RAM. Was this run with or without the l_partkey index that Jenny suggested? > > Here is an excerpt from my postgresql.conf file (the rest of the > settings are commented out): > > # > # Shared Memory Size > # > shared_buffers = 16384# 2*max_connections, min 16, typically > 8KB each > > # > # Non-shared Memory Sizes > # > sort_mem = 32768 > > # > # Optimizer Parameters > # > effective_cache_size = 32000 # typically 8KB each > > Any suggestions on how to optimize these settings? > > I agree with Jenny that declaring additional indexes on the TPC-R > tables may alter the validity of the benchmarks. Are there any > official TPC benchmarks submitted by PostgreSQL? Actually, for the TPC-R you _are allowed to declare additional indexes. With TPC-H you are restricted to a specific set listed in the spec (an index on l_partkey is allowed for both). What you cannot do for either TPC-R or TPC-H is rewrite the SQL of the query for the purposes of making the query run faster. Sorry if I was unclear. Valid TPC-R benchmark results are on the TPC web site: http://www.tpc.org/tpcr/default.asp I do not see one for PostgreSQL. Regards, Mary -- Mary Edie Meredith <[EMAIL PROTECTED]> Open Source Development Lab > > Thanks. > > Oleg > > -Original Message- > From: Mary Edie Meredith [mailto:[EMAIL PROTECTED] > Sent: Friday, September 26, 2003 10:12 AM > To: Tom Lane > Cc: Oleg Lebedev; Jenny Zhang; pgsql-performance > Subject: Re: [PERFORM] TPC-R benchmarks > > > The TPC-H/R rules allow only minor changes to the SQL that are > necessary due to SQL implementation differences. They do not allow > changes made to improve performance. It is their way to test > optimizer's ability to recognize an inefficient SQL statement and do > the rewrite. > > The rule makes sense for the TPC-H, which is supposed to represent > ad-Hoc query. One might argue that for TPC-R, which is suppose to > represent "Reporting" with pre-knowledge of the query, that re-write > should be allowed. However, that is currently not the case. Since the > RDBMS's represented on the TPC council are competing with TPC-H, their > optimizers already do the re-write, so (IMHO) there is no motivation > to relax the rules for the TPC-R. > > > On Thu, 2003-09-25 at 21:28, Tom Lane wrote: > > Oleg Lebedev <[EMAIL PROTECTED]> writes: > > > Seems like in your case postgres uses an i_l_partkey index on > > > lineitem table. I have a foreign key constraint defined between the > > > lineitem and part table, but didn't create an special indexes. Here > > > is my query plan: > > > > The planner is obviously unhappy with this plan (note the large cost > > numbers), but it can't find a way to do better. An index on > > lineitem.l_partkey would help, I think. > > > > The whole query seems like it's written in a very inefficient > > fashion; > > > couldn't the estimation of '0.2 * avg(l_quantity)' be amortized > > across > > > multiple join rows? But I dunno whether the TPC rules allow for > > significant manual rewriting of the given query. > > > > regards, tom lane > > > > ---(end of > > broadcast)--- >
Re: [PERFORM] TPC-R benchmarks
It took 10 hours to compute the query without the index on lineitem.l_partkey. Once I created the index on lineitem.l_partkey, it took only 32 secs to run the same query. After VACUUM ANALYZE it took 72 secs to run the query. All the subsequent runs took under 3 seconds! That's quite amazing! I just checked -Original Message- From: Mary Edie Meredith [mailto:[EMAIL PROTECTED] Sent: Monday, September 29, 2003 10:04 AM To: Oleg Lebedev Cc: Tom Lane; Jenny Zhang; pgsql-performance Subject: RE: [PERFORM] TPC-R benchmarks On Mon, 2003-09-29 at 07:35, Oleg Lebedev wrote: > I left my TPC-R query #17 working over the weekend and it took 3988 > mins ~ 10 hours to complete. And this is considering that I am using a > TPC-R database created with a scale factor of 1, which corresponds to > ~1 GB of data. I am running RedHat 8.0 on a dual 1 GHz processor, 512 > MB RAM. Was this run with or without the l_partkey index that Jenny suggested? > > Here is an excerpt from my postgresql.conf file (the rest of the > settings are commented out): > > # > # Shared Memory Size > # > shared_buffers = 16384# 2*max_connections, min 16, typically > 8KB each > > # > # Non-shared Memory Sizes > # > sort_mem = 32768 > > # > # Optimizer Parameters > # > effective_cache_size = 32000 # typically 8KB each > > Any suggestions on how to optimize these settings? > > I agree with Jenny that declaring additional indexes on the TPC-R > tables may alter the validity of the benchmarks. Are there any > official TPC benchmarks submitted by PostgreSQL? Actually, for the TPC-R you _are allowed to declare additional indexes. With TPC-H you are restricted to a specific set listed in the spec (an index on l_partkey is allowed for both). What you cannot do for either TPC-R or TPC-H is rewrite the SQL of the query for the purposes of making the query run faster. Sorry if I was unclear. Valid TPC-R benchmark results are on the TPC web site: http://www.tpc.org/tpcr/default.asp I do not see one for PostgreSQL. Regards, Mary -- Mary Edie Meredith <[EMAIL PROTECTED]> Open Source Development Lab > > Thanks. > > Oleg > > -Original Message- > From: Mary Edie Meredith [mailto:[EMAIL PROTECTED] > Sent: Friday, September 26, 2003 10:12 AM > To: Tom Lane > Cc: Oleg Lebedev; Jenny Zhang; pgsql-performance > Subject: Re: [PERFORM] TPC-R benchmarks > > > The TPC-H/R rules allow only minor changes to the SQL that are > necessary due to SQL implementation differences. They do not allow > changes made to improve performance. It is their way to test > optimizer's ability to recognize an inefficient SQL statement and do > the rewrite. > > The rule makes sense for the TPC-H, which is supposed to represent > ad-Hoc query. One might argue that for TPC-R, which is suppose to > represent "Reporting" with pre-knowledge of the query, that re-write > should be allowed. However, that is currently not the case. Since the > RDBMS's represented on the TPC council are competing with TPC-H, their > optimizers already do the re-write, so (IMHO) there is no motivation > to relax the rules for the TPC-R. > > > On Thu, 2003-09-25 at 21:28, Tom Lane wrote: > > Oleg Lebedev <[EMAIL PROTECTED]> writes: > > > Seems like in your case postgres uses an i_l_partkey index on > > > lineitem table. I have a foreign key constraint defined between the > > > lineitem and part table, but didn't create an special indexes. Here > > > is my query plan: > > > > The planner is obviously unhappy with this plan (note the large cost > > numbers), but it can't find a way to do better. An index on > > lineitem.l_partkey would help, I think. > > > > The whole query seems like it's written in a very inefficient > > fashion; > > > couldn't the estimation of '0.2 * avg(l_quantity)' be amortized > > across > > > multiple join rows? But I dunno whether the TPC rules allow for > > significant manual rewriting of the given query. > > > > regards, tom lane > > > > ---(end of > > broadcast)--- > > TIP 3: if posting/reading through Usenet, please send an appropriate > > subscribe-nomail command to [EMAIL PROTECTED] so that > your > > message can get through to the mailing list cleanly * 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 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [PERFORM] TPC-R benchmarks
I left my TPC-R query #17 working over the weekend and it took 3988 mins ~ 10 hours to complete. And this is considering that I am using a TPC-R database created with a scale factor of 1, which corresponds to ~1 GB of data. I am running RedHat 8.0 on a dual 1 GHz processor, 512 MB RAM. Here is an excerpt from my postgresql.conf file (the rest of the settings are commented out): # # Shared Memory Size # shared_buffers = 16384 # 2*max_connections, min 16, typically 8KB each # # Non-shared Memory Sizes # sort_mem = 32768 # # Optimizer Parameters # effective_cache_size = 32000# typically 8KB each Any suggestions on how to optimize these settings? I agree with Jenny that declaring additional indexes on the TPC-R tables may alter the validity of the benchmarks. Are there any official TPC benchmarks submitted by PostgreSQL? Thanks. Oleg -Original Message- From: Mary Edie Meredith [mailto:[EMAIL PROTECTED] Sent: Friday, September 26, 2003 10:12 AM To: Tom Lane Cc: Oleg Lebedev; Jenny Zhang; pgsql-performance Subject: Re: [PERFORM] TPC-R benchmarks The TPC-H/R rules allow only minor changes to the SQL that are necessary due to SQL implementation differences. They do not allow changes made to improve performance. It is their way to test optimizer's ability to recognize an inefficient SQL statement and do the rewrite. The rule makes sense for the TPC-H, which is supposed to represent ad-Hoc query. One might argue that for TPC-R, which is suppose to represent "Reporting" with pre-knowledge of the query, that re-write should be allowed. However, that is currently not the case. Since the RDBMS's represented on the TPC council are competing with TPC-H, their optimizers already do the re-write, so (IMHO) there is no motivation to relax the rules for the TPC-R. On Thu, 2003-09-25 at 21:28, Tom Lane wrote: > Oleg Lebedev <[EMAIL PROTECTED]> writes: > > Seems like in your case postgres uses an i_l_partkey index on > > lineitem table. I have a foreign key constraint defined between the > > lineitem and part table, but didn't create an special indexes. Here > > is my query plan: > > The planner is obviously unhappy with this plan (note the large cost > numbers), but it can't find a way to do better. An index on > lineitem.l_partkey would help, I think. > > The whole query seems like it's written in a very inefficient fashion; > couldn't the estimation of '0.2 * avg(l_quantity)' be amortized across > multiple join rows? But I dunno whether the TPC rules allow for > significant manual rewriting of the given query. > > regards, tom lane > > ---(end of > broadcast)--- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to [EMAIL PROTECTED] so that your > message can get through to the mailing list cleanly -- Mary Edie Meredith <[EMAIL PROTECTED]> Open Source Development Lab * 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 7: don't forget to increase your free space map settings
Re: [PERFORM] TPC-R benchmarks
Seems like in your case postgres uses an i_l_partkey index on lineitem
table. I have a foreign key constraint defined between the lineitem and
part table, but didn't create an special indexes. Here is my query plan:
-> Aggregate (cost=1517604222.32..1517604222.32 rows=1 width=31)
-> Hash Join (cost=8518.49..1517604217.39 rows=1969 width=31)
Hash Cond: ("outer".l_partkey = "inner".p_partkey)
Join Filter: ("outer".l_quantity < (subplan))
-> Seq Scan on lineitem (cost=0.00..241889.15
rows=6001215 widt
h=27)
-> Hash (cost=8518.00..8518.00 rows=197 width=4)
-> Seq Scan on part (cost=0.00..8518.00 rows=197
width=4)
Filter: ((p_brand = 'Brand#11'::bpchar) AND
(p_contai
ner = 'SM PKG'::bpchar))
SubPlan
-> Aggregate (cost=256892.28..256892.28 rows=1
width=11)
-> Seq Scan on lineitem (cost=0.00..256892.19
rows=37 w
idth=11)
Filter: (l_partkey = $0)
-Original Message-
From: Jenny Zhang [mailto:[EMAIL PROTECTED]
Sent: Thursday, September 25, 2003 3:33 PM
To: Oleg Lebedev
Cc: [EMAIL PROTECTED];
[EMAIL PROTECTED]
Subject: Re: [PERFORM] TPC-R benchmarks
I am running TPC-H with scale factor of 1 on RedHat7.2 with the kernel
2.5.74. Q17 can always finish in about 7 seconds on my system. The
execution plan is:
Aggregate (cost=780402.43..780402.43 rows=1 width=48)
-> Nested Loop (cost=0.00..780397.50 rows=1973 width=48)
Join Filter: ("inner".l_quantity < (subplan))
-> Seq Scan on part (cost=0.00..8548.00 rows=197 width=12)
Filter: ((p_brand = 'Brand#31'::bpchar) AND (p_container
= 'LG CASE'::bpchar))
-> Index Scan using i_l_partkey on lineitem
(cost=0.00..124.32 rows=30 width=36)
Index Cond: ("outer".p_partkey = lineitem.l_partkey)
SubPlan
-> Aggregate (cost=124.40..124.40 rows=1 width=11)
-> Index Scan using i_l_partkey on lineitem
(cost=0.00..124.32 rows=30 width=11)
Index Cond: (l_partkey = $0)
(11 rows)
Hope this helps,
Jenny
On Thu, 2003-09-25 at 12:40, Oleg Lebedev wrote:
> I am running TPC-R benchmarks with a scale factor of 1, which
> correspond to approximately 1 GB database size on PostgreSQL 7.3.4
> installed on CygWin on Windows XP. I dedicated 128 MB of shared memory
> to my postrges installation. Most of the queries were able to complete
> in a matter of minutes, but query 17 was taking hours and hours. The
> query is show below. Is there any way to optimize it ?
>
> select
> sum(l_extendedprice) / 7.0 as avg_yearly
> from
> lineitem,
> part
> where
> p_partkey = l_partkey
> and p_brand = 'Brand#11'
> and p_container = 'SM PKG'
> and l_quantity < (
> select
>0.2 * avg(l_quantity)
> from
>lineitem
> where
>l_partkey = p_partkey
> );
>
> Thanks.
>
> Oleg
>
> *
>
> 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.
>
> *
*
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 [EMAIL PROTECTED])
[PERFORM] TPC-R benchmarks
Title: Message I am running TPC-R benchmarks with a scale factor of 1, which correspond to approximately 1 GB database size on PostgreSQL 7.3.4 installed on CygWin on Windows XP. I dedicated 128 MB of shared memory to my postrges installation. Most of the queries were able to complete in a matter of minutes, but query 17 was taking hours and hours. The query is show below. Is there any way to optimize it ? select sum(l_extendedprice) / 7.0 as avg_yearlyfrom lineitem, partwhere p_partkey = l_partkey and p_brand = 'Brand#11' and p_container = 'SM PKG' and l_quantity < ( select 0.2 * avg(l_quantity) from lineitem where l_partkey = p_partkey ); Thanks. Oleg * 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. *
