On Mon, 2005-12-19 at 11:13 +1300, Mark Kirkwood wrote:
> > My understanding: Teradata and DB2 use this.
>
> FWIW - I think DB2 uses the successive fact RID buildup (i.e method 2),
> unfortunately
I think you're right; I was thinking about that point too because DB2
doesn't have index-organised
On Mon, 2005-12-19 at 11:10 +1300, Mark Kirkwood wrote:
> >>I found these two papers whilst browsing:
> >>
> >>
> >>http://www.cs.brown.edu/courses/cs227/Papers/Indexing/O'NeilGraefe.pdf
> >>http://www.dama.upc.edu/downloads/jaguilar-2005-4.pdf
> >>
> >>
> >>They seem to be describing a more subtle
Simon Riggs wrote:
On Sat, 2005-12-17 at 13:13 -0500, Tom Lane wrote:
Simon Riggs <[EMAIL PROTECTED]> writes:
On Fri, 2005-12-16 at 23:28 -0500, Bruce Momjian wrote:
How are star joins different from what we do now?
Methods:
1. join all N small tables together in a cartesian product, the
Simon Riggs wrote:
On Sun, 2005-12-18 at 15:02 +1300, Mark Kirkwood wrote:
Yeah - the quoted method of "make a cartesian product of the dimensions
and then join to the fact all at once" is not actually used (as written)
in many implementations
But it is used in some, which is why I mentio
Simon Riggs wrote:
On Sun, 2005-12-18 at 17:07 +1300, Mark Kirkwood wrote:
Tom Lane wrote:
2. transform joins into subselects, then return subselect rows via an
index bitmap. Joins are performed via a bitmap addition process.
Looks like 8.1 pretty much does this right now:
Good analysis
On Sun, 2005-12-18 at 17:07 +1300, Mark Kirkwood wrote:
> Tom Lane wrote:
>
> >>2. transform joins into subselects, then return subselect rows via an
> >>index bitmap. Joins are performed via a bitmap addition process.
>
> Looks like 8.1 pretty much does this right now:
Good analysis.
8.1 doesn
On Sat, 2005-12-17 at 13:13 -0500, Tom Lane wrote:
> Simon Riggs <[EMAIL PROTECTED]> writes:
> > On Fri, 2005-12-16 at 23:28 -0500, Bruce Momjian wrote:
> >> How are star joins different from what we do now?
>
> > Methods:
> > 1. join all N small tables together in a cartesian product, then join t
On Sun, 2005-12-18 at 15:02 +1300, Mark Kirkwood wrote:
> Yeah - the quoted method of "make a cartesian product of the dimensions
> and then join to the fact all at once" is not actually used (as written)
> in many implementations
But it is used in some, which is why I mentioned it.
I gave tw
Tom Lane <[EMAIL PROTECTED]> writes:
> Simon Riggs <[EMAIL PROTECTED]> writes:
> > On Fri, 2005-12-16 at 23:28 -0500, Bruce Momjian wrote:
> >> How are star joins different from what we do now?
>
> > Methods:
> > 1. join all N small tables together in a cartesian product, then join to
> > main La
Tom Lane wrote:
2. transform joins into subselects, then return subselect rows via an
index bitmap. Joins are performed via a bitmap addition process.
Looks like 8.1 pretty much does this right now:
First the basic star:
EXPLAIN ANALYZE
SELECT
d0.dmth,
d1.dat,
count(f
Tom Lane wrote:
Simon Riggs <[EMAIL PROTECTED]> writes:
On Fri, 2005-12-16 at 23:28 -0500, Bruce Momjian wrote:
How are star joins different from what we do now?
Methods:
1. join all N small tables together in a cartesian product, then join to
main Large table once (rather than N times)
Tom,
On 12/17/05 10:47 AM, "Tom Lane" <[EMAIL PROTECTED]> wrote:
> BTW, some experimentation suggests that in fact a star join is already
> slower than the "regular" plan in 8.1. You can force a star-join plan
> to be generated like this:
Cool!
We've got Paal's test case in the queue to run, i
Tom Lane wrote:
> Bruce Momjian writes:
> > Added to TODO:
> > * Allow star join optimizations
>
> See my response to Simon for reasons why this doesn't seem like a
> particularly good TODO item.
Yes, TODO removed. I thought we were waiting for bitmap joins before
trying star joins. I did not
I wrote:
> However, I submit that it wouldn't pick such a plan anyway, and should
> not, because the idea is utterly stupid.
BTW, some experimentation suggests that in fact a star join is already
slower than the "regular" plan in 8.1. You can force a star-join plan
to be generated like this:
reg
Bruce Momjian writes:
> Added to TODO:
> * Allow star join optimizations
See my response to Simon for reasons why this doesn't seem like a
particularly good TODO item.
regards, tom lane
---(end of broadcast)---
TIP 4: Have
Simon Riggs <[EMAIL PROTECTED]> writes:
> On Fri, 2005-12-16 at 23:28 -0500, Bruce Momjian wrote:
>> How are star joins different from what we do now?
> Methods:
> 1. join all N small tables together in a cartesian product, then join to
> main Large table once (rather than N times)
Of course, the
OK, so while our bitmap scan allows multiple indexes to be joined to get
to heap rows, a star joins allows multiple dimensions _tables_ to be
joined to index into a larger main fact table --- interesting.
Added to TODO:
* Allow star join optimizations
While our bitmap scan allows multiple ind
On Fri, 2005-12-16 at 23:28 -0500, Bruce Momjian wrote:
> How are star joins different from what we do now?
Various ways of doing them, but all use plans that you wouldn't have
come up with via normal join planning.
Methods:
1. join all N small tables together in a cartesian product, then join to
Bruce Momjian wrote:
How are star joins different from what we do now?
---
Recall that a "star" query with n tables means a query where there are
(n - 1) supposedly small tables (dimensions) and 1 large table (fact) -
w
How are star joins different from what we do now?
---
Simon Riggs wrote:
> On Thu, 2005-12-08 at 12:26 +0100, P?l Stenslet wrote:
> > I'm currently benchmarking several RDBMSs with respect to analytical
> > query performance
Simon,
> Yes, I'd expect something like this right now in 8.1; the
> numbers stack up to PostgreSQL doing equivalent join speeds,
> but w/o star join.
I do expect a significant improvement from 8.1 using the new bitmap index
because there is no need to scan the full Btree indexes. Also, the
On Thu, 2005-12-08 at 12:26 +0100, Pål Stenslet wrote:
> I'm currently benchmarking several RDBMSs with respect to analytical
> query performance on medium-sized multidimensional data sets. The data
> set contains 30,000,000 fact rows evenly distributed in a
> multidimensional space of 9 hierarchic
Tomeh
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Tom Lane
Sent: Sunday, December 11, 2005 12:39 PM
To: Pål Stenslet
Cc: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] Should Oracle outperform PostgreSQL on a complex
multidimensional query
Paal,
On 12/12/05 2:10 AM, "Pål Stenslet" <[EMAIL PROTECTED]> wrote:
> Here are the schema details, but first a little introduction:
Terrific, very helpful and thanks for both.
I wonder why the bitmap scan isn't selected in this query, Tom might have
some opinion and suggestions about it.
I'd
Paal,
> I'm currently benchmarking several RDBMSs with respect to
> analytical query performance on medium-sized multidimensional
> data sets. The data set contains 30,000,000 fact rows evenly
> distributed in a multidimensional space of 9 hierarchical
> dimensions. Each dimension has 8000 me
=?iso-8859-1?Q?P=E5l_Stenslet?= <[EMAIL PROTECTED]> writes:
> I have established similar conditions for the query in PostgreSQL, and =
> it runs in about 30 seconds. Again the CPU utilization is high with no =
> noticable I/O. The query plan is of course very different from that of =
> Oracle, sinc
I'm currently benchmarking several RDBMSs with respect to
analytical query performance on medium-sized multidimensional data sets. The
data set contains 30,000,000 fact rows evenly distributed in a multidimensional
space of 9 hierarchical dimensions. Each dimension has 8000 members.
The
27 matches
Mail list logo
