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

2008/1/20, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:
> Clodoaldo <clodoaldo(dot)pinto(dot)neto(at)gmail(dot)com> writes:
> > 2008/1/16, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:
> >> I don't know of any reason to think that insertion is slower in 8.3
> >> than it was in 8.2, and no one else has reported anything of the sort.
>
> > The old server reproduces the behavior of the new one.
>
> Okay, Clodoaldo kindly gave me access to his old server, and after
> nearly a full day of poking at it I think I've figured out what is going
> on.

Thank you very much for working on this. That server will still be
available for a while if you want to poke further.

> You can see from the query that it's inserting the same constant "data"
> value into every new row, and if I understand the context correctly this
> value will be higher than all prior entries in the "usuarios" table.

Yes, that is correct. That value is this:
nextval('datas_data_serial_seq'::regclass)

> From
> this we can see that 8.2 has a working set that is a relatively small
> part of the index at any instant, whereas 8.3 has the entire index as
> working set ... and it doesn't fit into RAM. Ergo, lots of disk seek
> delays.
...
> A possibly usable workaround for now is "set enable_hashagg = off"
> to force a GroupAggregate plan, which will deliver the values sorted
> by (data, usuario) rather than by their hash values. This helps both
> versions, bringing the runtime down to something like 250 seconds,
> because the index on usuario then has complete locality of access.
> Alternatively, doubling the server's RAM would probably make the problem
> go away (for awhile, until the index reaches 4GB).

I need to say that the new server has 4GB and suffers the same
symptoms. OTOH if the proposed solution works then I wonder if it
matters.

Will "enable_hashagg" be settable(*) per query or only at server start/reload?

Regards, Clodoaldo Pinto Neto

* I didn't find this world at the dictionary but also didn't find
another one to say it.