On Wed, Jul 18, 2007 at 09:13:14PM +0200, Thomas Finneid wrote:
Michael Stone wrote:
I don't understand how the insert you described is table to table?
SELECT INTO is table to table, so is INSERT INTO SELECT FROM.
I could have sworn that at least one of the examples you gave didn't
have any
Adriaan van Os wrote:
> So, how does one (temporarily) disable WAL logging ? Or, for example,
> disable WAL logging for a temporary table ?
Operations on temporary tables are never WAL logged. Operations on other
tables are, and there's no way to disable it.
--
Heikki Linnakangas
EnterpriseD
Michael Stone wrote:
On Tue, Jul 17, 2007 at 10:58:01PM +0200, Thomas Finneid wrote:
I am not sure I understand you correctly here, are you saying that
SELECT INTO in 8.1 disables WAL logging and uses just a single fsync
at the end? in that case it means that I could disable WAL as well and
ac
Michael Stone wrote:
On Tue, Jul 17, 2007 at 11:01:15PM +0200, Thomas Finneid wrote:
Arjen van der Meijden wrote:
Have you also tried the COPY-statement? Afaik select into is similar
to what happens in there.
No, because it only works on file to db or vice versa not table to table.
I don'
Michael Glaesemann wrote:
As they're individual inserts, I think what you're seeing is overhead
from calling this statement 100,000 times, not just on the server but
also the overhead through JDBC. For comparison, try
CREATE TABLE ciu_data_type_copy LIKE ciu_data_type;
INSERT INTO ciu_data_
PFC wrote:
Unfortunately its not fast enough, it needs to be done in no more than
1-2 seconds, ( and in production it will be maybe 20-50 columns of
data, perhaps divided over 5-10 tables.)
Additionally it needs to scale to perhaps three times as many columns
and perhaps 2 - 3 times as many ro
On Tue, Jul 17, 2007 at 11:01:15PM +0200, Thomas Finneid wrote:
Arjen van der Meijden wrote:
Have you also tried the COPY-statement? Afaik select into is similar to
what happens in there.
No, because it only works on file to db or vice versa not table to table.
I don't understand how the ins
On Tue, Jul 17, 2007 at 10:58:01PM +0200, Thomas Finneid wrote:
I am not sure I understand you correctly here, are you saying that
SELECT INTO in 8.1 disables WAL logging and uses just a single fsync at
the end? in that case it means that I could disable WAL as well and
achieve the same perform
It's the time to parse statements, plan, execute, roundtrips with
the client, context switches, time for your client library to escape
the data and encode it and for postgres to decode it, etc. In a word :
OVERHEAD.
I know there is some overhead, but that much when running it batched.
PFC wrote:
I was doing some testing on "insert" compared to "select into". I
inserted 100 000 rows (with 8 column values) into a table, which took
14 seconds, compared to a select into, which took 0.8 seconds.
(fyi, the inserts where batched, autocommit was turned off and it all
happend on
I was doing some testing on "insert" compared to "select into". I
inserted 100 000 rows (with 8 column values) into a table, which took 14
seconds, compared to a select into, which took 0.8 seconds.
(fyi, the inserts where batched, autocommit was turned off and it all
happend on the local m
Mark Lewis wrote:
If you're performing via JDBC, are you using addBatch/executeBatch, or
are you directly executing each insert? If you directly execute each
insert, then your code will wait for a server round-trip between each
insert.
I tested both and I found almost no difference in the ti
On Jul 17, 2007, at 15:50 , Thomas Finneid wrote:
Michael Glaesemann wrote:
2a) Are you using INSERT INTO foo (foo1, foo2, foo2) SELECT foo1,
foo2, foo3 FROM pre_foo or individual inserts for each row? The
former would be faster than the latter.
performed with JDBC
insert into ciu_dat
Arjen van der Meijden wrote:
Have you also tried the COPY-statement? Afaik select into is similar to
what happens in there.
No, because it only works on file to db or vice versa not table to table.
regards
thoams
---(end of broadcast)---
TIP
If you're performing via JDBC, are you using addBatch/executeBatch, or
are you directly executing each insert? If you directly execute each
insert, then your code will wait for a server round-trip between each
insert.
That still won't get you to the speed of select into, but it should
help. You
On Tue, Jul 17, 2007 at 10:50:22PM +0200, Thomas Finneid wrote:
I havent done this test in a stored function yet, nor have I tried it
with a C client so far, so there is the chance that it is java/jdbc that
makes the insert so slow. I'll get to that test soon if there is any
chance my theory ma
Tom Lane wrote:
Michael Glaesemann <[EMAIL PROTECTED]> writes:
It would be helpful if you included the actual queries you're using,
as there are a number of variables:
Not to mention which PG version he's testing.
Its pg 8.1, for now, I'll be upgrading to a compile optimised 8.2 when I
Michael Glaesemann wrote:
On Jul 17, 2007, at 14:38 , Thomas Finneid wrote:
I was doing some testing on "insert" compared to "select into". I
inserted 100 000 rows (with 8 column values) into a table, which took
14 seconds, compared to a select into, which took 0.8 seconds.
(fyi, the insert
Michael Glaesemann <[EMAIL PROTECTED]> writes:
> It would be helpful if you included the actual queries you're using,
> as there are a number of variables:
Not to mention which PG version he's testing. Since (I think) 8.1,
SELECT INTO knows that it can substitute one fsync for WAL-logging
the i
Have you also tried the COPY-statement? Afaik select into is similar to
what happens in there.
Best regards,
Arjen
On 17-7-2007 21:38 Thomas Finneid wrote:
Hi
I was doing some testing on "insert" compared to "select into". I
inserted 100 000 rows (with 8 column values) into a table, which t
On Jul 17, 2007, at 14:38 , Thomas Finneid wrote:
I was doing some testing on "insert" compared to "select into". I
inserted 100 000 rows (with 8 column values) into a table, which
took 14 seconds, compared to a select into, which took 0.8 seconds.
(fyi, the inserts where batched, autocommit
Hi
I was doing some testing on "insert" compared to "select into". I
inserted 100 000 rows (with 8 column values) into a table, which took 14
seconds, compared to a select into, which took 0.8 seconds.
(fyi, the inserts where batched, autocommit was turned off and it all
happend on the local m
22 matches
Mail list logo
