> Iain
> 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.
Agreed - very very interesting design though.
> My po
Iain wrote:
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.
Right.
My point in my previous post was that you could still
Hi, Steve,
On Wed, 15 Sep 2004 21:17:03 -0700
Steve Atkins <[EMAIL PROTECTED]> wrote:
> On Wed, Sep 15, 2004 at 11:16:44AM +0200, Markus Schaber wrote:
> > But you have to add table constraints restricting the time after adding
> > the partition?
>
> Uhm... unless I'm confused that's not a meani
Hi, Mischa,
On Tue, 14 Sep 2004 22:58:20 GMT
Mischa Sandberg <[EMAIL PROTECTED]> wrote:
> Googling 'upsert' (an Oraclism, I believe) will get you hits on Oracle
> and DB2's implementation of MERGE, which does what AMOUNTS to what is
> described below (one mass UPDATE...FROM, one mass INSERT...WH
AIL PROTECTED]>
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, [EMAIL PROTECTED] (Joe Conway) wrote:
> >>That's exactly what we'r
Christopher Browne wrote:
In the last exciting episode, [EMAIL PROTECTED] (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 applicat
derived from a parent table makes a lot of sense.
regards
Iain
- Original Message -
From: "Joe Conway" <[EMAIL PROTECTED]>
To: "Iain" <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>
Sent: Thursday, September 16, 2004 1:07 PM
Subject: Re: [PERFORM]
insert into X
select a.keyA,
a.keyB,
a.colA,
a.colB
from Y a left join X b
using (keyA, keyB)
where b.keyA is NULL and
b.keyB is NULL;
With the appropriate indexes, this is pretty fast but I think a merge
would be much faster.
Problem is it's subject to race
Googling 'upsert' (an Oraclism, I believe) will get you hits on Oracle
and DB2's implementation of MERGE, which does what AMOUNTS to what is
described below (one mass UPDATE...FROM, one mass INSERT...WHERE NOT
EXISTS).
No, you shouldn't iterate row-by-row through the temp table.
Whenever possibl
Simon Riggs wrote:
Jim C. Nasby
On Mon, Sep 13, 2004 at 11:07:35PM +0100, Simon Riggs wrote:
PostgreSQL's functionality is in many ways similar to Oracle
Partitioning.
Loading up your data in many similar tables, then creating a view like:
CREATE VIEW BIGTABLE (idate, col1, col2, col3...) AS
SELECT
On Wed, Sep 15, 2004 at 11:16:44AM +0200, Markus Schaber wrote:
> Hi,
>
> On Tue, 14 Sep 2004 22:10:04 -0700
> Steve Atkins <[EMAIL PROTECTED]> wrote:
>
> > > Is there by any chance a set of functions to manage adding and removing
> > > partitions? Certainly this can be done by hand, but having a
Iain 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
Simon Riggs wrote:
Joe,
Your application is very interesting. I've just read your OSCON paper. I'd
like to talk more about that. Very similar to Kalido.
...but back to partitioning momentarily: Does the performance gain come from
partition elimination of the inherited tables under the root?
I think
Joe,
Your application is very interesting. I've just read your OSCON paper. I'd
like to talk more about that. Very similar to Kalido.
...but back to partitioning momentarily: Does the performance gain come from
partition elimination of the inherited tables under the root?
Best Regards, Simon Ri
Chris Browne <[EMAIL PROTECTED]> wrote on 15.09.2004, 04:34:53:
> [EMAIL PROTECTED] ("Simon Riggs") writes:
> > Well, its fairly straightforward to auto-generate the UNION ALL view,
and
> > important as well, since it needs to be re-specified each time a new
> > partition is loaded or an old one i
Josh Berkus wrote:
- the use of inherited tables to partition this huge number of rows and
yet allow simple query access to it seems to work well, at least in
early validation tests
- had we simply taken the original database and "slammed" it into
Postgres with no further thought, we wou
Joe,
> - the use of inherited tables to partition this huge number of rows and
> yet allow simple query access to it seems to work well, at least in
> early validation tests
> - had we simply taken the original database and "slammed" it into
> Postgres with no further thought, we w
[EMAIL PROTECTED] wrote:
Joe Conway <[EMAIL PROTECTED]> wrote on 15.09.2004, 06:30:24:
We're not completely done with our data conversion (from a commercial
RDBMSi), but so far the results have been excellent. Similar to what
others have said in this thread, the conversion involved restructuring
Hi,
On Tue, 14 Sep 2004 22:10:04 -0700
Steve Atkins <[EMAIL PROTECTED]> wrote:
> > Is there by any chance a set of functions to manage adding and removing
> > partitions? Certainly this can be done by hand, but having a set of
> > tools would make life much easier. I just looked but didn't see an
Joe Conway <[EMAIL PROTECTED]> wrote on 15.09.2004, 06:30:24:
> Chris Browne wrote:
> > Might we set up the view as:
> >
> > create view combination_of_logs as
> > select * from table_1 where txn_date between 'this' and 'that'
> >union all
> > select * from table_2 where txn_date between
In the last exciting episode, [EMAIL PROTECTED] (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 pa
Hi,
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Michael Kleiser
Sent: Tuesday, September 14, 2004 4:23 PM
To: Leeuw van der, Tim
Cc: Steinar H. Gunderson; PostgreSQL Performance List
Subject: Re: [PERFORM] Data Warehouse Reevaluation - MySQL vs Postgres
On Tue, Sep 14, 2004 at 05:33:33PM -0500, Jim C. Nasby wrote:
> On Mon, Sep 13, 2004 at 11:07:35PM +0100, Simon Riggs wrote:
> > PostgreSQL's functionality is in many ways similar to Oracle Partitioning.
> >
> > Loading up your data in many similar tables, then creating a view like:
> >
> > CREAT
Hi Joe,
> 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
Chris Browne wrote:
Might we set up the view as:
create view combination_of_logs as
select * from table_1 where txn_date between 'this' and 'that'
union all
select * from table_2 where txn_date between 'this2' and 'that2'
union all
select * from table_3 where txn_date between 'this3'
[EMAIL PROTECTED] ("Simon Riggs") writes:
> Well, its fairly straightforward to auto-generate the UNION ALL view, and
> important as well, since it needs to be re-specified each time a new
> partition is loaded or an old one is cleared down. The main point is that
> the constant placed in front of
[EMAIL PROTECTED] ("Simon Riggs") wrote:
> The main point is that the constant placed in front of each table
> must in some way relate to the data, to make it useful in
> querying. If it is just a unique constant, chosen at random, it
> won't do much for partition elimination.
It just struck me -
On Sep 15, 2004, at 8:32 AM, Simon Riggs wrote:
The "partitions" are just tables, so no need for other management
tools.
Oracle treats the partitions as sub-tables, so you need a range of
commands
to add, swap etc the partitions of the main table.
I guess a set of tools that emulates that functi
> Jim C. Nasby
> On Mon, Sep 13, 2004 at 11:07:35PM +0100, Simon Riggs wrote:
> > PostgreSQL's functionality is in many ways similar to Oracle
> Partitioning.
> >
> > Loading up your data in many similar tables, then creating a view like:
> >
> > CREATE VIEW BIGTABLE (idate, col1, col2, col3...) AS
On Mon, Sep 13, 2004 at 11:07:35PM +0100, Simon Riggs wrote:
> PostgreSQL's functionality is in many ways similar to Oracle Partitioning.
>
> Loading up your data in many similar tables, then creating a view like:
>
> CREATE VIEW BIGTABLE (idate, col1, col2, col3...) AS
> SELECT 200409130800, col
> Stephen Frost
> * Markus Schaber ([EMAIL PROTECTED]) wrote:
> > Generally, what is the fastest way for doing bulk processing of
> > update-if-primary-key-matches-and-insert-otherwise operations?
>
> This is a very good question, and I havn't seen much of an answer to it
> yet. I'm curious about
From: "Harald Lau (Sector-X)" <[EMAIL PROTECTED]>
...
> From: "Mischa Sandberg" <[EMAIL PROTECTED]>
>
> > If your company is currently happy with MySQL, there probably are
> > other (nontechnical) reasons to stick with it. I'm impressed that
> > you'd consider reconsidering PG.
>
> I'd like to seco
> "MC" == Mark Cotner <[EMAIL PROTECTED]> writes:
MC> I've finished porting the schema and am importing the
MC> data now. My estimates for just two-thirds(60 of the
MC> 90 days) of one of our 30 cable systems(MySQL dbs) is
MC> estimated to take about 16 hours. This may seem like
MC> a lot, b
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
> From: "Mischa Sandberg" <[EMAIL PROTECTED]>
>
> > If your company is currently happy with MySQL, there probably are
> > other (nontechnical) reasons to stick with it. I'm impressed that
> > you'd consider reconsidering PG.
>
> I'd like to second Mi
* Markus Schaber ([EMAIL PROTECTED]) wrote:
> Generally, what is the fastest way for doing bulk processing of
> update-if-primary-key-matches-and-insert-otherwise operations?
This is a very good question, and I havn't seen much of an answer to it
yet. I'm curious about the answer myself, actuall
PROTECTED]
[mailto:[EMAIL PROTECTED] Behalf Of Steinar H.
Gunderson
Sent: Tuesday, September 14, 2004 3:33 PM
To: PostgreSQL Performance List
Subject: Re: [PERFORM] Data Warehouse Reevaluation - MySQL vs Postgres --
> On Tue, Sep 14, 2004 at 02:42:20PM +0200, Leeuw van der, Tim wrote:
>
In article <[EMAIL PROTECTED]>,
"Leeuw van der, Tim" <[EMAIL PROTECTED]> writes:
> So what I can say is, that if you want fast INSERT performance from
> PostgreSQL then you'll probably have to do some trickery that you
> wouldn't have to do with a default MySQL installation.
I think the word "INS
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Steinar H. Gunderson
Sent: Tuesday, September 14, 2004 3:33 PM
To: PostgreSQL Performance List
Subject: Re: [PERFORM] Data Warehouse Reevaluation - MySQL vs Postgres --
> On Tue, Sep 14, 2004 at 02:42:2
On Tue, Sep 14, 2004 at 02:42:20PM +0200, Leeuw van der, Tim wrote:
> - PostgreSQL 7.3 running on CYGWIN with cygipc daemon
Isn't this doomed to kill your performance anyhow?
/* Steinar */
--
Homepage: http://www.sesse.net/
---(end of broadcast)--
> Mark Cotner wrote:
> > The time has come to reevaluate/rearchitect an
> > application which I built about 3 years ago. There
> > are no performance concerns with MySQL, but it would
> > benefit greatly from stored procedures, views, etc.
>
From: "Mischa Sandberg" <[EMAIL PROTECTED]>
> If your
stallation.
regards,
--Tim
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Markus Schaber
Sent: Tuesday, September 14, 2004 2:15 PM
To: PostgreSQL Performance List
Subject: Re: [PERFORM] Data Warehouse Reevaluation - MySQL vs Postgres --
Hi, Mischa,
On Su
Hi, Mischa,
On Sun, 12 Sep 2004 20:47:17 GMT
Mischa Sandberg <[EMAIL PROTECTED]> wrote:
> On the other hand, if you do warehouse-style loading (Insert, or PG
> COPY, into a temp table; and then 'upsert' into the perm table), I can
> guarantee 2500 inserts/sec is no problem.
As we can forsee th
Performance hint :
For static data, do not normalize too much.
For instance if you have a row which can be linked to several other rows,
you can do this :
create table parents (
id serial primary key,
values... )
create table children (
id serial primary k
You all have been so very helpful so far and I really
appreciate it.
The data in these tables is thankfully static since
they are logging tables and an analyze only takes
about 4 minutes for the largest of them.
I've finished porting the schema and am importing the
data now. My estimates for jus
Mark Cotner wrote:
Hi all,
I had a difficult time deciding which list to post
this to, so please forgive me if this list doesn't
perfectly match my questions. My decision will not
solely be based on performance, but it is the primary
concern. I would be very appreciative if you all
could comment
Mark,
I thought some additional comments on top of Christopher's excellent notes
might help you.
> Christopher Browne
> The world rejoiced as Mischa Sandberg
> <[EMAIL PROTECTED]> wrote:
> > Mark Cotner wrote:
> >> Requirements:
> >> Merge table definition equivalent. We use these
> >> extensiv
A long time ago, in a galaxy far, far away, [EMAIL PROTECTED] (Mark Cotner) wrote:
> Agreed, I did some preliminary testing today and am very impressed.
> I wasn't used to running analyze after a data load, but once I did
> that everything was snappy.
Something worth observing is that this is true
See comments . . . thanks for the feedback.
'njoy,
Mark
--- Christopher Browne <[EMAIL PROTECTED]> wrote:
> The world rejoiced as Mischa Sandberg
> <[EMAIL PROTECTED]> wrote:
> > Mark Cotner wrote:
> >> Requirements:
> >> Merge table definition equivalent. We use these
> >> extensively.
>
> >
The world rejoiced as Mischa Sandberg <[EMAIL PROTECTED]> wrote:
> Mark Cotner wrote:
>> Requirements:
>> Merge table definition equivalent. We use these
>> extensively.
> Looked all over mysql.com etc, and afaics merge table is indeed
> exactly a view of a union-all. Is that right?
> PG support
Mark Cotner wrote:
Requirements:
Merge table definition equivalent. We use these
extensively.
What do you mean with "merge table definition equivalent"?
Regards
Gaetano Mendola
---(end of broadcast)---
TIP 2: you can get off all lists at once wit
On Sat, 11 Sep 2004, Mark Cotner wrote:
> There are no performance concerns with MySQL, but it would benefit
> greatly from stored procedures, views, etc. It is a very large rolling
> data warehouse that inserts about 4.5 million rows every 2 hours and
> subsequently rolls this data off the back
51 matches
Mail list logo
