Problem solved.. I set the fetchSize to a reasonable value instead of
the default of unlimited in the PreparedStatement and now the query
is . After some searching it seeems this is a common problem, would it
make sense to change the default value to something other than 0 in
the JDBC driver?
If
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
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
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
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]
* 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
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. The
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
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
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
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
24 matches
Mail list logo