Re: ATTN: Clodaldo was Performance problem. Could it be related to 8.3-beta4? - Mailing list pgsql-general

2008/1/10, Clodoaldo <clodoaldo.pinto.neto@gmail.com>:
> 2008/1/10, Tom Lane <tgl@sss.pgh.pa.us>:
> > It would be interesting to see the identical test on Clodaldo's
> > installations.
>
> This is 8.2.6 in the new server:
>
> cpn=> create table foo (f1 int, f2 int, f3 int, f4 real);
> CREATE TABLE
> cpn=> create index fooi on foo(f1);
> CREATE INDEX
> cpn=> create index fooi2 on foo(f2);
> CREATE INDEX
> cpn=> explain analyze insert into foo select i,i,0,1.0 from
> generate_series(1,1000000) i;
>                                                           QUERY PLAN
>
------------------------------------------------------------------------------------------------------------------------------
>  Function Scan on generate_series i  (cost=0.00..12.50 rows=1000
> width=4) (actual time=270.425..699.067 rows=1000000 loops=1)
>   Total runtime: 12888.913 ms
> (2 rows)
>
> The table into which I'm inserting 800 thousand rows, usuarios, has
> 135 million rows so I did:
>
> cpn=> explain analyze insert into foo select i,i,0,1.0 from
> generate_series(1,135500000) i;
>
>                                                              QUERY
> PLAN
>
-------------------------------------------------------------------------------------------------------------------------------------
>  Function Scan on generate_series i  (cost=0.00..12.50 rows=1000
> width=4) (actual time=49852.161..403976.519 rows=135500000 loops=1)
>   Total runtime: 2044745.294 ms
> (2 rows)
> cpn=> analyze;
> ...warnings...
> ANALYZE
> cpn=> explain analyze insert into foo select i,i,0,1.0 from
> generate_series(135500001, 135500000 + 800000 ) i;
>                                                          QUERY PLAN
>
-----------------------------------------------------------------------------------------------------------------------------
>  Function Scan on generate_series i  (cost=0.00..12.50 rows=1000
> width=4) (actual time=196.804..553.617 rows=800000 loops=1)
>   Total runtime: 11202.895 ms
> (2 rows)

Now the same with 8.3RC1 at the new server:

cpn=> create table foo (f1 int, f2 int, f3 int, f4 real);
CREATE TABLE
cpn=> create index fooi on foo(f1);
CREATE INDEX
cpn=> create index fooi2 on foo(f2);
CREATE INDEX
cpn=> explain analyze insert into foo select i,i,0,1.0 from
generate_series(1,1000000) i;
                                                          QUERY PLAN

-------------------------------------------------------------------------------------------------------------------------------
 Function Scan on generate_series i  (cost=0.00..12.50 rows=1000
width=4) (actual time=495.995..1103.326 rows=1000000 loops=1)
 Total runtime: 13380.214 ms
(2 rows)

cpn=> explain analyze insert into foo select i,i,0,1.0 from
generate_series(1,1000000) i;
                                                          QUERY PLAN

-------------------------------------------------------------------------------------------------------------------------------
 Function Scan on generate_series i  (cost=0.00..12.50 rows=1000
width=4) (actual time=469.872..1111.901 rows=1000000 loops=1)
 Total runtime: 18640.398 ms
(2 rows)

cpn=> explain analyze insert into foo select i,i,0,1.0 from
generate_series(1,1000000) i;
                                                          QUERY PLAN

------------------------------------------------------------------------------------------------------------------------------
 Function Scan on generate_series i  (cost=0.00..12.50 rows=1000
width=4) (actual time=294.671..929.198 rows=1000000 loops=1)
 Total runtime: 16704.956 ms
(2 rows)

cpn=> explain analyze insert into foo select i,i,0,1.0 from
cpn-> generate_series(1,135500000) i;
                                                             QUERY
PLAN

-------------------------------------------------------------------------------------------------------------------------------------
 Function Scan on generate_series i  (cost=0.00..12.50 rows=1000
width=4) (actual time=54032.804..342699.642 rows=135500000 loops=1)
 Total runtime: 1687252.668 ms
(2 rows)

cpn=> analyze;
...warnings...
ANALYZE
cpn=> explain analyze insert into foo select i,i,0,1.0 from
cpn-> generate_series(135500001, 135500000 + 800000 ) i;
                                                         QUERY PLAN

-----------------------------------------------------------------------------------------------------------------------------
 Function Scan on generate_series i  (cost=0.00..12.50 rows=1000
width=4) (actual time=244.565..733.050 rows=800000 loops=1)
 Total runtime: 9689.809 ms
(2 rows)

Regards, Clodoaldo Pinto Neto