От: Alvaro Herrera
Тема: Re: TRUNCATE TABLE
Дата: ,
Msg-id: 20070713173501.GD12109@alvh.no-ip.org
(см: обсуждение, исходный текст)
Ответ на: Re: TRUNCATE TABLE  (Tom Lane)
Ответы: Re: TRUNCATE TABLE  ("Pavel Stehule")
Список: pgsql-performance

Скрыть дерево обсуждения

TRUNCATE TABLE  (Adriaan van Os, )
 Re: TRUNCATE TABLE  (Tom Lane, )
  Re: TRUNCATE TABLE  (Adriaan van Os, )
   Re: TRUNCATE TABLE  (Tom Lane, )
 Re: TRUNCATE TABLE  (Gregory Stark, )
  Re: TRUNCATE TABLE  (Adriaan van Os, )
   Re: TRUNCATE TABLE  (Gregory Stark, )
   Re: TRUNCATE TABLE  (Jean-Max Reymond, )
    Re: TRUNCATE TABLE  ("Thomas Samson", )
   Re: TRUNCATE TABLE  (Michael Stone, )
    Re: TRUNCATE TABLE  (Tom Lane, )
     Re: TRUNCATE TABLE  (Adriaan van Os, )
      Re: TRUNCATE TABLE  ("Steinar H. Gunderson", )
      Re: TRUNCATE TABLE  (Tom Lane, )
       Re: TRUNCATE TABLE  ("Jim C. Nasby", )
       Re: TRUNCATE TABLE  (Alvaro Herrera, )
        Re: TRUNCATE TABLE  ("Pavel Stehule", )
         Re: TRUNCATE TABLE  ("Jim C. Nasby", )
          Re: TRUNCATE TABLE  (Tom Lane, )
           Re: TRUNCATE TABLE  (Adriaan van Os, )
           Re: TRUNCATE TABLE  (Adriaan van Os, )
            Re: TRUNCATE TABLE  (Alvaro Herrera, )
           Re: TRUNCATE TABLE  ("Kevin Grittner", )
            Re: TRUNCATE TABLE  (Adriaan van Os, )
             Re: TRUNCATE TABLE  (Decibel!, )
              Re: TRUNCATE TABLE  (Tom Lane, )
       Re: TRUNCATE TABLE  (Adriaan van Os, )

Tom Lane wrote:
> Adriaan van Os <> writes:
> > I started another test. I copied an existing database (not very large,
> > 35 tables, typically a few hundred up to a few thousand records) with
> > CREATE DATABASE testdb TEMPLATE mydb and started to remove random
> > tables from testdb with DROP TABLE and TRUNCATE TABLE. I did this with
> > the query tool of pgAdmin III, to exclude any doubts about my own
> > software (that uses pqlib).
>
> Can you try it with plain psql?  pgAdmin is a variable that wasn't
> accounted for in my tests.
>
> > The hardware is an Intel dual-core 17-inch
> > MacBook Pro running Mac OS X 10.4.
>
> Hmm.  I thought you said Fedora before.  However, I'd done a few tests
> yesterday on my own Mac laptop (Al G4) and not gotten results that were
> out of line with HPUX or Fedora.
>
> Does anyone else want to try replicating these tests?

I notice that the times are sometimes different when the table is TEMP.
DROP TABLE times are sometimes in the vicinity of 13ms and at other
times 200ms.  My test is

vacuum pg_class; vacuum pg_type; vacuum pg_attribute;
create temp table van_os (a int);
insert into van_os select * from generate_series(1, 200000); drop table van_os;

passed as a single line to psql (no -c).

Times are closer to 2ms when the table has only 5000 tuples.




Doing this
insert into van_os select * from generate_series(1, 200000); truncate van_os;

I get about 200ms on the truncate step.

Whereas if I do this
insert into van_os select * from generate_series(1, 5000); truncate van_os;
times are closer to 8-13 ms.

I guess the difference is the amount of data that ext3 is logging on its
journal.  My ext3 journal settings are default.

--
Alvaro Herrera       Valdivia, Chile   ICBM: S 39º 49' 18.1", W 73º 13' 56.4"
"¿Que diferencia tiene para los muertos, los huérfanos, y aquellos que han
perdido su hogar, si la loca destrucción ha sido realizada bajo el nombre
del totalitarismo o del santo nombre de la libertad y la democracia?" (Gandhi)


В списке pgsql-performance по дате сообщения:

От: Alvaro Herrera
Дата:
Сообщение: Re: Database Statistics???
От: Hannes Dorbath
Дата:
Сообщение: Re: FORGOT TO CONFIGURE RAID! DELL POWEREDGE 2950