Re: No flamefest please, MySQL vs. PostgreSQL AGAIN

On Tue, 2003-05-13 at 00:02, timeless postgres wrote:
...
> Maybe I don't understand how to ask the question. I want to know how to
> insert (say) 10M rows into a table quickly. I mean... VERY quickly.
>
> Obviously the following 10M transactions are going to be slow:
>
>         insert into tab values (1);
>         insert into tab values (2);
>         insert into tab values (3);
>          . . .
>         insert into tab values (10000000);
>
> Would it be faster if I put a single transaction around that?

10M transactions are going to be slow. 10M inserts in one transaction or
in a few transactions will be a lot faster.

> Would it
> be faster to do the following?
>
>         copy tab (col) from stdin;
>         1
>         2
>         3
>          . . .
>         10000000
>         \.

Yes, that will be the fastest of all.

> > It should be noted though that as of
> > 7.3, operations on TEMP tables don't do WAL logging; perhaps that
> > would be of use to you.
>
> It does sound useful, on new tables, to load into a temp table, then
> rename the temp table as permanent table.

I don't think that is possible.

--
Oliver Elphick                                Oliver.Elphick@lfix.co.uk
Isle of Wight, UK                             http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839  932A 614D 4C34 3E1D 0C1C
                 ========================================
     "Watch ye and pray, lest ye enter into temptation. The
      spirit truly is ready, but the flesh is weak."
                           Mark 14:38