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

2008/1/10, Clodoaldo <clodoaldo(dot)pinto(dot)neto(at)gmail(dot)com>:
> 2008/1/10, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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