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
I have a table with ~8 million rows and I am executing a query which
should return about ~800,000 rows. The problem is that as soon as I
execute the query it absolutely kills my machine and begins swapping
for 5 or 6 minutes before it begins returning results. Is postgres
trying to load the whole
> "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
> "TL" == Tom Lane <[EMAIL PROTECTED]> writes:
TL> Bruce Momjian <[EMAIL PROTECTED]> writes:
>> If we had a majority of queries filling more than one block we would
>> be checkpointing like crazy and we don't normally get reports about
>> that.
TL> [ raised eyebrow... ] And of course the 30-
Hi, Stephen,
On Mon, 13 Sep 2004 19:51:22 -0500
Stephen Crowley <[EMAIL PROTECTED]> wrote:
> Does postgres cache the entire result set before it begins returning
> data to the client?
>
> I have a table with ~8 million rows and I am executing a query which
> should return about ~800,000 rows. Th
-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
What MySQl-table-type did you use?
Was it "MyISAM" which don't supports transactions ?
Yes I read about that bulk-inserts with this table-type are very fast.
In Data Warehouse one often don't need transactions.
Leeuw van der, Tim schrieb:
-Original Message-
From: [EMAIL PROTECTED]
[mailto:
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:20PM +02
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
Hi,
I found bulk-insert to perform very slow, when compared to MySQL / Oracle. All inserts
were done in 1 transaction. However, mitigating factors here were:
- Application was a .Net application using ODBC drivers
- PostgreSQL 7.3 running on CYGWIN with cygipc daemon
- Probably very bad tuning in
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
26 matches
Mail list logo
