Re: Data Warehouse Reevaluation - MySQL vs Postgres --

Joe, Christopher,

Joe's example wasn't excluding partions, as he didn't use a predicated UNION
ALL view to select from. His queries use an indexed column that allow the
various partitions to be probed at low cost, and he was satisfied wth that.

My point in my previous post was that you could still do all that that if
you wanted to, by building the predicated view with UNION ALL of each of the
child tables.

regards
Iain
----- Original Message -----
From: "Joe Conway" <mail(at)joeconway(dot)com>
To: "Christopher Browne" <cbbrowne(at)acm(dot)org>
Cc: <pgsql-performance(at)postgresql(dot)org>
Sent: Thursday, September 16, 2004 2:17 PM
Subject: Re: [PERFORM] Data Warehouse Reevaluation - MySQL vs Postgres --

> Christopher Browne wrote:
> > In the last exciting episode, mail(at)joeconway(dot)com (Joe Conway) wrote:
> >>That's exactly what we're doing, but using inherited tables instead of
> >>a union view. With inheritance, there is no need to rebuild the view
> >>each time a table is added or removed. Basically, in our application,
> >>tables are partitioned by either month or week, depending on the type
> >>of data involved, and queries are normally date qualified.
>
> > Where does the constraint come in that'll allow most of the data to be
> > excluded?
>
> Not sure I follow this.
>
> > Or is this just that the entries are all part of "bigtable" so that
> > the self join is only 2-way?
>
> We don't have a need for self-joins in our application. We do use a
> crosstab function to materialize some transposed views of the data,
> however. That allows us to avoid self-joins in the cases where we might
> otherwise need them.
>
> Joe
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo(at)postgresql(dot)org)