Tatsuo Ishii <[EMAIL PROTECTED]> writes:
>> Just for quick note, it seems query 19 takes forever. Have you
>> successfully run Q19?
> Here is the more detailed info. The query was not finished within 3
> days and was canceled on a Dual Xeon 2.8GHz with 2.5GB RAM running
> Linux. PostgreSQL is 7.4.
> > > Hi Tatsuo,
> > >
> > > I've made a new release:
> > > http://prdownloads.sourceforge.net/osdldbt/dbt3-v1.5.tar.gz?download
> > >
> > > Let me know if there are any problems.
> >
> > Thanks!
>
> Just for quick note, it seems query 19 takes forever. Have you
> successfully run Q19?
Here
> > Hi Tatsuo,
> >
> > I've made a new release:
> > http://prdownloads.sourceforge.net/osdldbt/dbt3-v1.5.tar.gz?download
> >
> > Let me know if there are any problems.
>
> Thanks!
Just for quick note, it seems query 19 takes forever. Have you
successfully run Q19?
--
Tatsuo Ishii
-
> Hi Tatsuo,
>
> I've made a new release:
> http://prdownloads.sourceforge.net/osdldbt/dbt3-v1.5.tar.gz?download
>
> Let me know if there are any problems.
Thanks!
> On Tue, Oct 26, 2004 at 12:44:49PM +0900, Tatsuo Ishii wrote:
> > Hi,
> >
> > Thanks for the info. Would you give me the t
Hi Tatsuo,
I've made a new release:
http://prdownloads.sourceforge.net/osdldbt/dbt3-v1.5.tar.gz?download
Let me know if there are any problems.
On Tue, Oct 26, 2004 at 12:44:49PM +0900, Tatsuo Ishii wrote:
> Hi,
>
> Thanks for the info. Would you give me the tarball?
-
Hi,
Thanks for the info. Would you give me the tarball?
--
Tatsuo Ishii
> Hi Tatsuo,
>
> Yes, I've been updating the dbt3 kit over the past several months.
> The query time graph is a new feature. It's available via BitKeeper
> at bk://developer.osdl.org:/var/bk/dbt3 but I haven't tested the ki
Hi Tatsuo,
Yes, I've been updating the dbt3 kit over the past several months.
The query time graph is a new feature. It's available via BitKeeper
at bk://developer.osdl.org:/var/bk/dbt3 but I haven't tested the kit
well enough to make a v1.5 release yet. If BitKeeper isn't something
you can use,
Tatsuo Ishii schrieb:
I see nice graphs for each DBT3 query(for example,
http://developer.osdl.org/markw/dbt3-pgsql/42/q_time.png). It seems
they do not come with normal dbt3-1.4 kit. How did you get them?
Maybe you have slightly modified dbt3 kit?
This looks like a simple ploticus one-liner.
like:
Mark,
I see nice graphs for each DBT3 query(for example,
http://developer.osdl.org/markw/dbt3-pgsql/42/q_time.png). It seems
they do not come with normal dbt3-1.4 kit. How did you get them?
Maybe you have slightly modified dbt3 kit?
--
Tatsuo Ishii
> On 6 Feb, To: [EMAIL PROTECTED] wrote:
> > On
On 12 Feb, Tom Lane wrote:
> [EMAIL PROTECTED] writes:
>> Ok, I have EXPLAIN ANALYZE results for both the power and throughput
>> tests:
>> http://developer.osdl.org/markw/dbt3-pgsql/
>
> Thanks. I just looked at Q9 and Q21, since those are the slowest
> queries according to your chart. (Ar
Mark,
> It's run #60 and the links are towards the bottom of the page under the
> "Run log data" heading. The results from the power test is
> "power_query.result" and "thuput_qs1.result", etc. for each stream in
> the throughput test.
I'm confused. Were you able to get the original-form query
Mark,
> Ok, I've found that the kit does capture "explain" results and I've
> added a "Query Plans" links under the query time charts on each of the
> pages. Um, but I did notice a couple of problems. It looks liks one of
> the 22 queries is missing and they're not labeled. I'll see about
> get
On 9 Feb, Tom Lane wrote:
> [EMAIL PROTECTED] writes:
>> I'll see what I can do about the "explain" and "explain analyze"
>> results. I remember in the past that someone said it would be most
>> interesting to execute the latter while the test while running, as
>> opposed to before or after a tes
On 16 Feb, Tom Lane wrote:
> [EMAIL PROTECTED] writes:
>> It looks like we have indexes on all of the date columns except
>> l_commitdate, which appears to be in Q4.
>
>> So I think I'll run against the CVS tip as is, again with an index on
>> l_commitdate, and then another test to confirm your th
[EMAIL PROTECTED] writes:
> It looks like we have indexes on all of the date columns except
> l_commitdate, which appears to be in Q4.
> So I think I'll run against the CVS tip as is, again with an index on
> l_commitdate, and then another test to confirm your theory. Sound good?
Sure, it's only
On 16 Feb, Tom Lane wrote:
> [EMAIL PROTECTED] writes:
>> I ran a test with the CAST you recommended for Q4 over the weekend:
>> http://developer.osdl.org/markw/dbt3-pgsql/68/
>> But it didn't seem to have much of an affect on Q4, compared to run
>> #66. I'll still give the CVS tip a try.
>
[EMAIL PROTECTED] writes:
> I ran a test with the CAST you recommended for Q4 over the weekend:
> http://developer.osdl.org/markw/dbt3-pgsql/68/
> But it didn't seem to have much of an affect on Q4, compared to run
> #66. I'll still give the CVS tip a try.
Hm. Disappointing. I can see fro
On 15 Feb, Tom Lane wrote:
> I wrote:
>> I see what is going on to make Q4 slow, too. It's this:
>> where o_orderdate >= date '1995-04-01' and o_orderdate < date '1995-04-01' +
>> interval '3 month'
>> ...
>> As of CVS tip the issue could be eliminated by introducing
>> cross-data-type comparis
[EMAIL PROTECTED] wrote:
On 12 Feb, Tom Lane wrote:
http://developer.osdl.org/markw/dbt3-pgsql/62/
This run changes default_statistics_target to 1000 and I have p_partkey,
l_partkey, ps_suppkey, and l_suppkey pg_stats here at 1 min intervals
http (no links on the web page.) Pretty significant
On 16 Feb, Dennis Haney wrote:
> [EMAIL PROTECTED] wrote:
>
>>On 12 Feb, Tom Lane wrote:
>>
>>
>>http://developer.osdl.org/markw/dbt3-pgsql/62/
>>
>>This run changes default_statistics_target to 1000 and I have p_partkey,
>>l_partkey, ps_suppkey, and l_suppkey pg_stats here at 1 min intervals
>>
On Mon, 2004-02-09 at 16:53, Josh Berkus wrote:
> Jenny,
>
> > For 19, we moved the common conditions out of the big ORs, for 20, we
> > added distinct. We can change the query back if the optimizer can
> > handle it now.
>
> Well, we want to test if it can.
Replace the file 19.sql under datage
On Mon, 2004-02-09 at 11:39, [EMAIL PROTECTED] wrote:
> On 9 Feb, Josh Berkus wrote:
> > Mark,
> >
> >> Ok, I've found that the kit does capture "explain" results and I've
> >> added a "Query Plans" links under the query time charts on each of the
> >> pages. Um, but I did notice a couple of pro
I wrote:
> I see what is going on to make Q4 slow, too. It's this:
> where o_orderdate >= date '1995-04-01' and o_orderdate < date '1995-04-01' +
> interval '3 month'
> ...
> As of CVS tip the issue could be eliminated by introducing
> cross-data-type comparison operators between types date and
[EMAIL PROTECTED] writes:
> http://developer.osdl.org/markw/dbt3-pgsql/66/
> There's a run with a modified Q21. Made a huge improvement in Q21.
Okay, looks like we know what we need to attack to solve Q21... actually
solving it will be a tad harder ;-) but we understand where the problem is.
I
Dennis Haney <[EMAIL PROTECTED]> writes:
> You are refering to:
> @inproceedings{ hellerstein93predicate,
> author = "Joseph M. Hellerstein and Michael Stonebraker",
> title = "Predicate migration: optimizing queries with expensive
> predicates",
Yup, I sure am. This is the same thesis r
You are refering to:
@inproceedings{ hellerstein93predicate,
author = "Joseph M. Hellerstein and Michael Stonebraker",
title = "Predicate migration: optimizing queries with expensive
predicates",
pages = "267--276",
year = "1993",
abstract = "The traditional focus of relational que
On 12 Feb, Josh Berkus wrote:
> Mark,
>
>> Oh sorry, I completely forgot that Q19 the whole purpose of this. So
>> #60 doesn't have the right Q19. I'll run with the one you want now.
>
> Thanks! And the original, not the "fixed", Q19 if you please. It's the
> original that wouldn't finish o
[EMAIL PROTECTED] writes:
> Ok, I have EXPLAIN ANALYZE results for both the power and throughput
> tests:
> http://developer.osdl.org/markw/dbt3-pgsql/
Thanks. I just looked at Q9 and Q21, since those are the slowest
queries according to your chart. (Are all the queries weighted the same
f
Mark,
> Oh sorry, I completely forgot that Q19 the whole purpose of this. So
> #60 doesn't have the right Q19. I'll run with the one you want now.
Thanks! And the original, not the "fixed", Q19 if you please. It's the
original that wouldn't finish on Postgres 7.3.
--
-Josh Berkus
Aglio D
On 12 Feb, Josh Berkus wrote:
> Mark,
>
>> It's run #60 and the links are towards the bottom of the page under the
>> "Run log data" heading. The results from the power test is
>> "power_query.result" and "thuput_qs1.result", etc. for each stream in
>> the throughput test.
>
> I'm confused. Wer
Ok, I have EXPLAIN ANALYZE results for both the power and throughput
tests:
http://developer.osdl.org/markw/dbt3-pgsql/
It's run #60 and the links are towards the bottom of the page under the
"Run log data" heading. The results from the power test is
"power_query.result" and "thuput_qs1.r
Jenny,
> For 19, we moved the common conditions out of the big ORs, for 20, we
> added distinct. We can change the query back if the optimizer can
> handle it now.
Well, we want to test if it can.
--
Josh Berkus
Aglio Database Solutions
San Francisco
---(end of broadc
Josh Berkus <[EMAIL PROTECTED]> writes:
> If #19 is missing it's because Oleg & I could not get it to complete. That
> was also the query which we are most interested in testing.
Q19 doesn't seem to be particularly slow in either the 7.4 or 7.5 tests
--- there are many others with longer runtime
On 9 Feb, Josh Berkus wrote:
> Mark,
>
>> Ok, I've found that the kit does capture "explain" results and I've
>> added a "Query Plans" links under the query time charts on each of the
>> pages. Um, but I did notice a couple of problems. It looks liks one of
>> the 22 queries is missing and they
[EMAIL PROTECTED] writes:
> http://developer.osdl.org/markw/dbt3-pgsql/
> There's a short summary of the tests I ran over the weekend, with links
> to detailed retults. Comparing runs 43 (7.4) and 52 (7.5devel), it
> looks like query #7 had the only significant improvement. Oprofile data
> shoul
On 9 Feb, Tom Lane wrote:
> [EMAIL PROTECTED] writes:
>> http://developer.osdl.org/markw/dbt3-pgsql/
>
>> There's a short summary of the tests I ran over the weekend, with links
>> to detailed retults. Comparing runs 43 (7.4) and 52 (7.5devel), it
>> looks like query #7 had the only significant
[EMAIL PROTECTED] writes:
> I'll see what I can do about the "explain" and "explain analyze"
> results. I remember in the past that someone said it would be most
> interesting to execute the latter while the test while running, as
> opposed to before or after a test. Should I do that here too?
I
On 6 Feb, To: [EMAIL PROTECTED] wrote:
> On 5 Jan, Tom Lane wrote:
>> Josh Berkus <[EMAIL PROTECTED]> writes:
>>> 2) DEVELOP BETTER PLANS FOR "OR GROUP" QUERIES
>>
>>> Summary: Currently, queries with complex "or group" criteria get devolved by
>>> the planner into canonical and-or filters resu
On 6 Feb, Tom Lane wrote:
> [EMAIL PROTECTED] writes:
>> creating template1 database in /opt/pgdb/dbt2/base/1 ... ERROR: relnatts disagrees
>> with indnatts for index 16601
>
> Wow, that's a bizarre one. Are you sure you did a clean rebuild?
> I usually like to do "make distclean" before or af
[EMAIL PROTECTED] writes:
> creating template1 database in /opt/pgdb/dbt2/base/1 ... ERROR: relnatts disagrees
> with indnatts for index 16601
Wow, that's a bizarre one. Are you sure you did a clean rebuild?
I usually like to do "make distclean" before or after "cvs update";
it tends to save me
On 6 Feb, Tom Lane wrote:
> [EMAIL PROTECTED] writes:
>> Sorry it's taking so long. I tried to take a export from CVS today and
>> the database appears not to be able to connect to the postmaster when I
>> attempt to create the database. Let me know if getting a trace of
>> anything will help, i
[EMAIL PROTECTED] writes:
> Sorry it's taking so long. I tried to take a export from CVS today and
> the database appears not to be able to connect to the postmaster when I
> attempt to create the database. Let me know if getting a trace of
> anything will help, if you guys already aren't already
On 5 Jan, Tom Lane wrote:
> Josh Berkus <[EMAIL PROTECTED]> writes:
>> 2) DEVELOP BETTER PLANS FOR "OR GROUP" QUERIES
>
>> Summary: Currently, queries with complex "or group" criteria get devolved by
>> the planner into canonical and-or filters resulting in very poor execution on
>> large data s
Tom,
> I've made some progress on this over the last week or two. Would it be
> possible to retry that benchmark with CVS tip?
Yes! I'll just need some time to get my laptop set up for running it. My
server is, alas, in storage due to me being "between offices".
--
-Josh Berkus
Aglio Da
On 5 Jan, Tom Lane wrote:
> Josh Berkus <[EMAIL PROTECTED]> writes:
>> 2) DEVELOP BETTER PLANS FOR "OR GROUP" QUERIES
>
>> Summary: Currently, queries with complex "or group" criteria get devolved by
>> the planner into canonical and-or filters resulting in very poor execution on
>> large data s
Josh Berkus <[EMAIL PROTECTED]> writes:
> 2) DEVELOP BETTER PLANS FOR "OR GROUP" QUERIES
> Summary: Currently, queries with complex "or group" criteria get devolved by
> the planner into canonical and-or filters resulting in very poor execution on
> large data sets. We should find better ways o
I'm not going to be able to set this up. I just had to put my server into
cold storage due to dismantling my office, and running the TPC stuff on my
laptop is a joke.
I'll contact the OSDL folks to see if they can run it.
We can... depending on what you need for a server.
J
--
Comma
Tom,
> Could we see the actual present query plans for both the TPC-R query
> and the UNION version? (I'll settle for "explain" on the slow
> version, but "explain analyze" on the other, please.)
I'm not going to be able to set this up. I just had to put my server into
cold storage due to dis
Tom,
> In general I am suspicious of proposals to rewrite queries into UNION
> "equivalents", because the "equivalent" usually isn't exactly
> equivalent, at least not without conditions that the planner can't
> easily prove.
As I said, I'm not sure that UNIONing the query is the solution, we jus
Josh Berkus <[EMAIL PROTECTED]> writes:
> Summary: Currently, queries with complex "or group" criteria get devolved by
> the planner into canonical and-or filters resulting in very poor execution on
> large data sets. We should find better ways of dealing with these queries,
> for example UNION
I know Oracle is capable of producing the UNION plan. but I don't know if
that's the only option. I'm curious what indexes the rewritten union-based
query used.
Josh Berkus <[EMAIL PROTECTED]> writes:
> SELECT t1.a, t2.b
> FROM t1, t2
> WHERE t1.a = t2.a
> AND (
> ( t1.c = x
> AND
John,
> > SELECT t1.a, t2.b
> > FROM t1, t2
> > WHERE t1.a = t2.a
> > AND t1.c = x
> > AND t1.f IN (m, n, o)
> > AND t2.d = v
> > AND t2.e BETWEEN j AND k
> > UNION ALL
> Shouldn't that be "UNION" instead of "UNION ALL"? You don't want
> duplicate rows, if i'm not mistaken.
PG Folks,
What follows are a couple of proposed TODO items to make up for some of the
places our planner is weak compared to other leading databases.
Particularly, I'm personally concerned that as of 7.4.0 we would "fail" the
TPC benchmark even if someone sponsored us for it (see Issue #2 bel
53 matches
Mail list logo