Re: crazy logging from PG 8.4

On 23.10.2013 20:56, Jon Nelson wrote:
> On Wed, Oct 23, 2013 at 9:54 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> "Tomas Vondra" <tv@fuzzy.cz> writes:
>>> On 23 Říjen 2013, 16:00, Jon Nelson wrote:
>>>> After several days of running a simple query over a very large dataset
>>>> (more than a terabyte), I logged in to note that postgresql had
>>>> decided to log *billions* of log lines.
>>
>>> First guess is that you enabled detailed logging in config.
>>
>> I think it's trace_sort.
>
> I think you are right.
>
> Let's say that there would end up being 2.7+ billion groups.  Does
> that scenario help to explain? I guess I'd like to see trace_sort have
> a new value in addition to just "on" and "off", perhaps "external".  I
> don't really care about internal sorts but I do care about external
> sorts. Right now, they're the only (minimal!) insight I have into how
> the query is progressing.

Yes, that sounds plausible, because COUNT(DISTINCT) ends with a sort for
each group. At least that's my experience.

If by internal/external you mean in-memory/on-disk sorting, then you can
disable trace_sort and use log_temp_files instead. It will probably log
some other operations using temp files, but it's unlikely to happen as
frequently as trace_sort.

Tomas