Re: [PERFORM] Data Warehouse Reevaluation - MySQL vs Postgres --

2004-09-17 Thread Simon Riggs
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 point in

Re: [PERFORM] Data Warehouse Reevaluation - MySQL vs Postgres --

2004-09-16 Thread Iain
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're doing, but using inherited tables instead of a union view. With inheritance, there is no need

Re: [PERFORM] Data Warehouse Reevaluation - MySQL vs Postgres --

2004-09-16 Thread Markus Schaber
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...WHERE

Re: [PERFORM] Data Warehouse Reevaluation - MySQL vs Postgres --

2004-09-16 Thread Markus Schaber
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 meaningful

Re: [PERFORM] Data Warehouse Reevaluation - MySQL vs Postgres --

2004-09-16 Thread Joe Conway
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

Re: [PERFORM] Data Warehouse Reevaluation - MySQL vs Postgres --

2004-09-15 Thread Leeuw van der, Tim
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

Re: [PERFORM] Data Warehouse Reevaluation - MySQL vs Postgres --

2004-09-15 Thread Christopher Browne
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

Re: Re: [PERFORM] Data Warehouse Reevaluation - MySQL vs Postgres --

2004-09-15 Thread simon
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 'this2' and

Re: [PERFORM] Data Warehouse Reevaluation - MySQL vs Postgres --

2004-09-15 Thread Markus Schaber
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 anything

Re: [PERFORM] Data Warehouse Reevaluation - MySQL vs Postgres --

2004-09-15 Thread Joe Conway
[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

Re: [PERFORM] Data Warehouse Reevaluation - MySQL vs Postgres --

2004-09-15 Thread Josh Berkus
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 would

Re: [PERFORM] Data Warehouse Reevaluation - MySQL vs Postgres --

2004-09-15 Thread Joe Conway
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

Re: [PERFORM] Data Warehouse Reevaluation - MySQL vs Postgres -- merge tables?

2004-09-15 Thread Simon Riggs
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 is cleared

Re: [PERFORM] Data Warehouse Reevaluation - MySQL vs Postgres --

2004-09-15 Thread Simon Riggs
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

Re: [PERFORM] Data Warehouse Reevaluation - MySQL vs Postgres --

2004-09-15 Thread Joe Conway
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

Re: [PERFORM] Data Warehouse Reevaluation - MySQL vs Postgres --

2004-09-15 Thread Joe Conway
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

Re: [PERFORM] Data Warehouse Reevaluation - MySQL vs Postgres --

2004-09-15 Thread Steve Atkins
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 set of

Re: [PERFORM] Data Warehouse Reevaluation - MySQL vs Postgres -- merge tables

2004-09-15 Thread Mischa Sandberg
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

Re: [PERFORM] Data Warehouse Reevaluation - MySQL vs Postgres --

2004-09-15 Thread Mischa Sandberg
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

Re: [PERFORM] Data Warehouse Reevaluation - MySQL vs Postgres --

2004-09-15 Thread Christopher Kings-Lynne
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

Re: [PERFORM] Data Warehouse Reevaluation - MySQL vs Postgres --

2004-09-15 Thread Iain
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] Data Warehouse Reevaluation - MySQL vs Postgres

Re: [PERFORM] Data Warehouse Reevaluation - MySQL vs Postgres --

2004-09-15 Thread Joe Conway
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

Re: [PERFORM] Data Warehouse Reevaluation - MySQL vs Postgres -- merge tables

2004-09-14 Thread Pierre-Frdric Caillaud
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

Re: [PERFORM] Data Warehouse Reevaluation - MySQL vs Postgres --

2004-09-14 Thread Markus Schaber
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 that

Re: [PERFORM] Data Warehouse Reevaluation - MySQL vs Postgres --

2004-09-14 Thread Leeuw van der, Tim
, --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 Sun, 12 Sep 2004 20:47

Re: [PERFORM] Data Warehouse Reevaluation - MySQL vs Postgres -- merge tables

2004-09-14 Thread Aaron Werman
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 company is

Re: [PERFORM] Data Warehouse Reevaluation - MySQL vs Postgres --

2004-09-14 Thread Steinar H. Gunderson
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

Re: [PERFORM] Data Warehouse Reevaluation - MySQL vs Postgres --

2004-09-14 Thread Leeuw van der, Tim
-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:20PM

Re: [PERFORM] Data Warehouse Reevaluation - MySQL vs Postgres --

2004-09-14 Thread Harald Fuchs
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 INSERT is

Re: [PERFORM] Data Warehouse Reevaluation - MySQL vs Postgres --

2004-09-14 Thread Michael Kleiser
:[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: - PostgreSQL 7.3 running

Re: [PERFORM] Data Warehouse Reevaluation - MySQL vs Postgres --

2004-09-14 Thread 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 the answer myself,

Re: [PERFORM] Data Warehouse Reevaluation - MySQL vs Postgres -- merge tables

2004-09-14 Thread Harald Lau (Sector-X)
-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 Mischa on that

Re: [PERFORM] Data Warehouse Reevaluation - MySQL vs Postgres -- merge tables

2004-09-14 Thread Vivek Khera
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, but I'm

Re: [PERFORM] Data Warehouse Reevaluation - MySQL vs Postgres -- merge tables

2004-09-14 Thread aaron werman
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 second Mischa on that

Re: [PERFORM] Data Warehouse Reevaluation - MySQL vs Postgres -- merge tables

2004-09-14 Thread 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 200409130800, col1,

Re: [PERFORM] Data Warehouse Reevaluation - MySQL vs Postgres -- merge tables

2004-09-14 Thread Simon Riggs
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

Re: [PERFORM] Data Warehouse Reevaluation - MySQL vs Postgres -- merge tables

2004-09-14 Thread Michael Glaesemann
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

Re: [PERFORM] Data Warehouse Reevaluation - MySQL vs Postgres -- merge tables

2004-09-14 Thread Christopher Browne
[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 - this

Re: [PERFORM] Data Warehouse Reevaluation - MySQL vs Postgres -- merge tables

2004-09-14 Thread Chris Browne
[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 each

Re: [PERFORM] Data Warehouse Reevaluation - MySQL vs Postgres --

2004-09-14 Thread Joe Conway
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'

Re: [PERFORM] Data Warehouse Reevaluation - MySQL vs Postgres --

2004-09-14 Thread Iain
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

Re: [PERFORM] Data Warehouse Reevaluation - MySQL vs Postgres -- merge tables

2004-09-14 Thread Steve Atkins
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: CREATE VIEW

Re: [PERFORM] Data Warehouse Reevaluation - MySQL vs Postgres -- merge tables

2004-09-13 Thread Mark Cotner
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. Looked all over

Re: [PERFORM] Data Warehouse Reevaluation - MySQL vs Postgres -- merge tables

2004-09-13 Thread Christopher Browne
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

Re: [PERFORM] Data Warehouse Reevaluation - MySQL vs Postgres -- merge tables

2004-09-13 Thread Simon Riggs
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 extensively. Looked

Re: [PERFORM] Data Warehouse Reevaluation - MySQL vs Postgres -- merge tables

2004-09-13 Thread Mischa Sandberg
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

Re: [PERFORM] Data Warehouse Reevaluation - MySQL vs Postgres

2004-09-12 Thread Gaetano Mendola
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 with

Re: [PERFORM] Data Warehouse Reevaluation - MySQL vs Postgres

2004-09-11 Thread Dennis Bjorklund
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 end