2008/1/7, Scott Marlowe <scott.marlowe@xxxxxxxxx>:
> On Jan 6, 2008 1:46 PM, Clodoaldo <clodoaldo.pinto.neto@xxxxxxxxx> wrote:
> > 2008/1/6, Scott Marlowe <scott.marlowe@xxxxxxxxx>:
> >
> > > On Jan 6, 2008 5:06 AM, Clodoaldo <clodoaldo.pinto.neto@xxxxxxxxx> wrote:
> > > >
> > > > Then I rebuilt and reinstalled postgresql with the xlog_seg_size set
> > > > to the default 16MB and did initdb. Now the time is 7,642 sec.
> > > >
> > > > I'm lost. It looks like 1GB xlog_seg_size is indeed faster than 16MB
> > > > but again it is slower than the production server which uses the
> > > > default xlog_seg_size.
> > >
> > > How fast was it the second time you ran it?
> >
> > You mean the new server with 16MB xlog_seg_size? Yes, I did run it
> > twice and both took about the same time. In all tests (all
> > configurations) I did an analyze before running so I think that is the
> > real time.
>
> OK, to eliminate the chance that it's sick hardware, I would suggest
> installing 8.2.5 with exactly the same settings and build (as much as
> possible) and see how that works. If it's still slow, I would suspect
> the hardware is making the difference and investigate that first.
> Once you get even performance from 8.2.5 on both sets of hardware,
> then you can make a valid comparison with 8.3b4. Unless you've already
> done that... then I don't have a clue what to do...
I just did it. Built and installed 8.2.5. Copied the postgresql.conf
from the production. Issued an analyze and ran the insert query twice:
fahstats=> insert into usuarios (
fahstats(> data,
fahstats(> usuario,
fahstats(> pontos,
fahstats(> wus
fahstats(> )
fahstats-> select
fahstats-> (select data_serial from data_serial) as data,
fahstats-> ui.usuario_serial as usuario,
fahstats-> sum(pontos) as pontos,
fahstats-> sum(wus) as wus
fahstats-> from usuarios_temp as ut inner join usuarios_indice as ui
fahstats-> on ut.usuario = ui.usuario_nome and ut.n_time = ui.n_time
fahstats-> group by data, ui.usuario_serial
fahstats-> ;
INSERT 0 880479
Time: 384991.682 ms
The second time it ran in 403 sec, half the production time.
Regards, Clodoaldo Pinto Neto
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
