Received: from maia.hub.org (maia-5.hub.org [200.46.204.29]) by mail.postgresql.org (Postfix) with ESMTP id 50001133798F for ; Wed, 27 Oct 2010 15:07:04 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.29]) (amavisd-maia, port 10024) with ESMTP id 78804-06 for ; Wed, 27 Oct 2010 18:06:57 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from lpo-relay-05.vmsinfo.com (lpo-relay-05.vmsinfo.com [216.169.137.116]) by mail.postgresql.org (Postfix) with ESMTP id 06C3C1337B74 for ; Wed, 27 Oct 2010 15:06:55 -0300 (ADT) X-IronPort-AV: E=Sophos;i="4.58,247,1286164800"; d="scan'208,217";a="29650334" Received: from unknown (HELO lpo-mailbox-01.vmsinfo.com) ([216.169.137.252]) by lpo-relay-05.vmsinfo.com with ESMTP; 27 Oct 2010 14:06:55 -0400 Received: from [10.101.43.35] (unknown [216.169.135.235]) by lpo-mailbox-01.vmsinfo.com (Postfix) with ESMTP id E31BB7D00B; Wed, 27 Oct 2010 14:06:52 -0400 (EDT) Message-ID: <4CC86A3D.8000507@vmsinfo.com> Date: Wed, 27 Oct 2010 14:06:53 -0400 From: Mladen Gogala User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.2.11) Gecko/20101013 Lightning/1.0b2 Thunderbird/3.1.5 MIME-Version: 1.0 To: Scott Carey CC: "pgsql-performance@postgresql.org" Subject: Re: Postgres insert performance and storage requirement compared to Oracle References: <402913.53404.qm@web65405.mail.ac4.yahoo.com> <208728.42197.qm@web65409.mail.ac4.yahoo.com> <770715.39613.qm@web29003.mail.ird.yahoo.com> <4CC741FF.8050709@vmsinfo.com> <4CC74E31.6060307@vmsinfo.com> In-Reply-To: Content-Type: multipart/alternative; boundary="------------050907090808070301050509" X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.909 tagged_above=-10 required=5 tests=BAYES_00=-1.9, HTML_MESSAGE=0.001, T_RP_MATCHES_RCVD=-0.01 X-Spam-Level: X-Archive-Number: 201010/486 X-Sequence-Number: 40904 This is a multi-part message in MIME format. --------------050907090808070301050509 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit On 10/27/2010 1:48 PM, Scott Carey wrote: > > It is almost always significantly faster than a direct bulk load into a table. > * The temp table has no indexes, the final table usually does, bulk operations on indexes are faster than per row operations. > * The final table might require both updates and inserts, doing these in bulk from a temp stage table is far faster than per row. > * You don't even have to commit after the merge from the temp table, and can loop until its all done, then commit -- though this can have table/index bloat implications if doing updates. Scott, I find this very hard to believe. If you are inserting into a temporary table and then into the target table, you will do 2 inserts instead of just one. What you are telling me is that it is faster for me to drive from NYC to Washington DC by driving first to Miami and then from Miami to DC. >> 2) This is what I had in mind: >> >> mgogala=# create table a(c1 int); >> CREATE TABLE >> mgogala=# create temporary table t1(c1 int) on commit delete rows; >> CREATE TABLE >> mgogala=# begin; >> BEGIN >> mgogala=# insert into t1 select generate_series(1,1000); >> INSERT 0 1000 >> mgogala=# insert into a select * from t1; >> INSERT 0 1000 >> mgogala=# commit; >> COMMIT >> mgogala=# select count(*) from a; >> count >> ------- >> 1000 >> (1 row) >> >> mgogala=# select count(*) from t1; >> count >> ------- >> 0 >> (1 row) >> >> The table is created with "on commit obliterate rows" option which means >> that there is no need to do "truncate". The "truncate" command is a >> heavy artillery. Truncating a temporary table is like shooting ducks in >> a duck pond, with a howitzer. > ??? Test it. DELETE is slow, truncate is nearly instantaneous for normal tables. For temp tables its the same thing. Maybe in Oracle TRUNCATE is a howitzer, in Postgres its lightweight. Truncate has specific list of tasks to do: 1) lock the table in the exclusive mode to prevent concurrent transactions on the table. 2) Release the file space and update the table headers. 3) Flush any buffers possibly residing in shared memory. 4) Repeat the procedures on the indexes. Of course, in case of the normal table, all of these changes are logged, possibly producing WAL archives. That is still much faster than delete which depends on the number of rows that need to be deleted, but not exactly lightweight, either. In Postgres, truncate recognizes that the table is a temporary table so it makes a few shortcuts, which makes the truncate faster. 1) No need to flush buffers. 2) Locking requirements are much less stringent. 3) No WAL archives are produced. Temporary tables are completely different beasts in Oracle and Postgres. Yes, you are right, truncate of a temporary table is a big no-no in the Oracle world, especially in the RAC environment. However, I do find "ON COMMIT DELETE ROWS" trick to be more elegant than the truncate. Here is the classic Tom Kyte, on the topic of truncating the temporary tables: *http://tinyurl.com/29kph3p "*NO. truncate is DDL. DDL is expensive. Truncation is something that should be done very infrequently. Now, I don't mean "turn your truncates into DELETE's" -- that would be even worse. I mean -- avoid having to truncate or delete every row in the first place. Use a transaction based temporary table and upon commit, it'll empty itself." > Your loop above requires a commit after every 1000 rows. What if you require that all rows are seen at once or not at all? What if you fail part way through? One big transaction is often a better idea and/or required. Especially in postgres, with no undo-log, bulk inserts in one large transaction work out very well -- usually better than multiple smaller transactions. I don't contest that. I also prefer to do things in one big transaction, if possible. -- Mladen Gogala Sr. Oracle DBA 1500 Broadway New York, NY 10036 (212) 329-5251 http://www.vmsinfo.com The Leader in Integrated Media Intelligence Solutions --------------050907090808070301050509 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit On 10/27/2010 1:48 PM, Scott Carey wrote: 

It is almost always significantly faster than a direct bulk load into a table.  
* The temp table has no indexes, the final table usually does, bulk operations on indexes are faster than per row operations.
* The final table might require both updates and inserts, doing these in bulk from a temp stage table is far faster than per row.
* You don't even have to commit after the merge from the temp table, and can loop until its all done, then commit -- though this can have table/index bloat implications if doing updates.

Scott, I find this very hard to believe. If you are inserting into a temporary table and then into the target table, you will do 2 inserts instead of just one. What you are telling me is that it is faster for me to drive from NYC to Washington DC by driving first to Miami and then from Miami to DC.


      

        
2) This is what I had in mind:

mgogala=# create table a(c1 int);
CREATE TABLE
mgogala=# create temporary table t1(c1 int) on commit delete rows;
CREATE TABLE
mgogala=# begin;
BEGIN
mgogala=# insert into t1 select generate_series(1,1000);
INSERT 0 1000
mgogala=# insert into a select * from t1;
INSERT 0 1000
mgogala=# commit;
COMMIT
mgogala=# select count(*) from a;
 count
-------
  1000
(1 row)

mgogala=# select count(*) from t1;
 count
-------
     0
(1 row)

The table is created with "on commit obliterate rows" option which means 
that there is no need to do "truncate". The "truncate" command is a 
heavy artillery. Truncating a temporary table is like shooting ducks in 
a duck pond, with a howitzer.


      

      
???  Test it.  DELETE is slow, truncate is nearly instantaneous for normal tables.  For temp tables its the same thing.  Maybe in Oracle TRUNCATE is a howitzer, in Postgres its lightweight.  

    

    

    Truncate has specific list of tasks to do: 

    1)  lock the table in the exclusive mode to prevent concurrent
    transactions on the table.

    2)  Release the file space and update the table headers.

    3)  Flush any buffers possibly residing in shared memory.

    4)  Repeat the procedures on the indexes.

    

    Of course, in case of the normal table, all of these changes are
    logged, possibly producing WAL archives. That is still much faster
    than delete which depends on the number of rows that need to be
    deleted, but not exactly lightweight, either. In Postgres, truncate
    recognizes that the table is a temporary table so it makes a few
    shortcuts, which makes the truncate faster.

    

    1) No need to flush buffers.

    2) Locking requirements are much less stringent.

    3) No WAL archives are produced.

    

    Temporary tables are completely different beasts in Oracle and
    Postgres. Yes, you are right, truncate of a temporary table is a big
    no-no in the Oracle world, especially in the RAC environment.
    However, I do find "ON COMMIT DELETE ROWS" trick to be more elegant
    than the truncate. Here is the classic Tom Kyte, on the topic of
    truncating the temporary tables: http://tinyurl.com/29kph3p

      

      "NO. truncate is DDL. DDL is expensive. Truncation is
    something that should be done very infrequently. 

     Now, I don't mean "turn your truncates into DELETE's" -- that
    would  be even worse. I mean -- avoid having 

     to truncate or delete every row in the first  place. Use a
    transaction based temporary table and upon commit, it'll empty
    itself."

    

    

      
Your loop above requires a commit after every 1000 rows.  What if you require that all rows are seen at once or not at all?  What if you fail part way through?  One big transaction is often a better idea and/or required.  Especially in postgres, with no undo-log, bulk inserts in one large transaction work out very well -- usually better than multiple smaller transactions.


    

    

    I don't contest that. I also prefer to do things in one big
    transaction, if possible.

    

    
-- 
 
Mladen Gogala 
Sr. Oracle DBA
1500 Broadway
New York, NY 10036
(212) 329-5251
http://www.vmsinfo.com 
The Leader in Integrated Media Intelligence Solutions




  


--------------050907090808070301050509--