On Thu, 2003-09-25 at 14:32, Jenny Zhang wrote:
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:
I just want to point out that we are the OSDL are not running
a TPC-X anything. We
Tom,
I've found the problem with TPC-R query #19. And it, unfortunately, appears
to be a problem in the PostgreSQL query planner.
To sum up the below: it appears that whenever a set of WHERE conditions
exceeds a certain level of complexity, the planner just ignores all
applicable indexes
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
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
]
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
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
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
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
Tom Lane wrote:
When benchmarking with data sets considerably larger than available
buffer cache, I rather doubt that small random_page_cost would be a
good idea. Still, you might as well experiment to see.
From experience, I know the difference in response time can be huge when postgres
Oleg,
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.
Even though it takes hours to
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
; 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
: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
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
Oleg,
All right, my query just finished running with EXPLAIN ANALYZE.
I show the plan below and also attached it as a file.
Any ideas?
Yes. Your problem appears to be right here:
- Nested Loop
(cost=0.00..54596.00 rows=3 width=88) (actual
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
: [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
!
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
Shridhar Daithankar [EMAIL PROTECTED] writes:
Also if you have fast disk drives, you can reduce random page cost to 2 or 1.5.
Note however that most of the people who have found smaller
random_page_cost to be helpful are in situations where most of their
data fits in RAM. Reducing the cost
-
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
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
I'm pretty certain that there are no TPC-certified test results for
Postgres, because to date no organization has cared to spend the money
needed to perform a certifiable test.
Anyone have a rough idea of the costs involved?
- --
Greg Sabino
On Mon, Sep 29, 2003 at 05:43:26PM -, [EMAIL PROTECTED] wrote:
Anyone have a rough idea of the costs involved?
I did a back-of-an-envelope calculation one day and stopped when I
got to $10,000.
A
--
Andrew Sullivan 204-4141 Yonge Street
Afilias Canada
: 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
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
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
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
, 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
Jenny,
create index i_l_partkey on lineitem (l_partkey);
I do not have any foreign key defined. Does the spec require foreign
keys?
When you create a foreign key reference, does PG create an index
automatically?
No. A index is not required to enforce a foriegn key, and is sometimes not
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
29 matches
Mail list logo
