Josh Berkus <[EMAIL PROTECTED]> writes:
> 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 and goes for a seq scan.
It looks to me like the planner is coercing the WHERE clause into
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 and
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. W
:[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 stil
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
ill 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
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 i
, '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'
);
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
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 s
riginal 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 enab
Oleg,
> I ran VACUUM FULL ANALYZE yesterday and the re-ran the query with
> EXPLAIN ANALYZE.
> I got the same query plan and execution time.
How about my question? Those rows from pg_stats would be really useful in
diagnosing the problem.
--
Josh Berkus
Aglio Database Solutions
San Francisco
erkey)
> -> 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".p
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
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 time=
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
-> 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
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
1: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? Ca
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
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 s
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 t
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
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 pos
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_ye
: 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 i
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 k
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
-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
-
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
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 to
azing!
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
Mary Edie Meredith <[EMAIL PROTECTED]> writes:
> 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.
I'm pretty certain that there are no TPC-certified test results for
Postgres, because to date no organization has cared
th [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 t
Oleg Lebedev wrote:
effective_cache_size = 32000 # typically 8KB each
That is 256MB. You can raise it to 350+MB if nothing else is running on the box.
Also if you have fast disk drives, you can reduce random page cost to 2 or 1.5.
I don't know how much this will make any difference to benchmark r
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
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
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 wi
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
892.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 PROTECTE
[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.
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
42 matches
Mail list logo
