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

2008/1/11, Clodoaldo <clodoaldo(dot)pinto(dot)neto(at)gmail(dot)com>:
> 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)

Tom,

I guess the samples above are not very useful. If you think it would
help, i can upload the database dump, along with the source txt files
and the insert script, to some web directory, so you can see it
working. Its bziped size is 914MB so I will only upload it if you say
yes.

Regards, Clodoaldo Pinto Neto