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
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
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
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
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
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
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
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
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
[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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
,
--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
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
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
-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
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
:[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
* 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,
-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
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
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
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,
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 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
[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
[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
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'
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
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
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
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
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 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
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 on my test plan.
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
48 matches
Mail list logo
