pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Pavel Stehule <pavel.stehule@gmail.com>
To: Jon Nelson <jnelson+pgsql@jamponi.net>
Cc: pgsql-performance@postgresql.org
Subject: Re: postmaster consuming /lots/ of memory with hash aggregate. why?
Date: Fri, 12 Nov 2010 17:12:26 +0100
Message-ID: <AANLkTin2eL+Xy8Gh5dOUTc6RH-ehyFu+eCcxHkVG4g4i@mail.gmail.com> (raw)
In-Reply-To: <AANLkTikv9BT3=5C1aXCJomej0gQxTkv3TX1DeiMjA-P-@mail.gmail.com>
References: <AANLkTi=MOeC+D_1tUEACxt43Xv1x0EtHDjN23Hp5yuXW@mail.gmail.com>
	<AANLkTikgSPy+q0zRv5LLETa9NK7tP6GL9EThjk55-okP@mail.gmail.com>
	<AANLkTik9RA2YqQszZJZP90Wb0NbkhoMWJAz0gJoknPLi@mail.gmail.com>
	<AANLkTim6CnN1ubjnuRahV3p-UBaMUzD5nMWtQQ3batCE@mail.gmail.com>
	<AANLkTi=_igsVFkEg2H=90_aJpCrA2Q9FSFurTyLLu74m@mail.gmail.com>
	<AANLkTikv9BT3=5C1aXCJomej0gQxTkv3TX1DeiMjA-P-@mail.gmail.com>

2010/11/12 Jon Nelson <jnelson+pgsql@jamponi.net>:
> On Thu, Nov 11, 2010 at 10:38 PM, Pavel Stehule <pavel.stehule@gmail.com> wrote:
>> 2010/11/12 Jon Nelson <jnelson+pgsql@jamponi.net>:
>>> On Thu, Nov 11, 2010 at 10:26 PM, Pavel Stehule <pavel.stehule@gmail.com> wrote:
>>>> Hello
>>>>
>>>> look on EXPLAIN ANALYZE command. Probably your statistic are out, and
>>>> then planner can be confused. EXPLAIN ANALYZE statement show it.
>>>
>>> As I noted earlier, I did set statistics to 1000 an re-ran vacuum
>>> analyze and the plan did not change.
>>
>> this change can do nothing. this is default in config. did you use
>> ALTER TABLE ALTER COLUMN SET STATISTIC = ... ? and ANALYZE
>
> No. To be clear: are you saying that changing the value for
> default_statistics_target, restarting postgresql, and re-running
> VACUUM ANALYZE does *not* change the statistics for columns
> created/populated *prior* to the sequence of operations, and that one
> /must/ use ALTER TABLE ALTER COLUMN SET STATISTICS ... and re-ANALYZE?
>

yes.

but I was wrong. Documentation is correct. Problem is elsewhere.

> That does not jive with the documentation, which appears to suggest
> that setting a new default_statistics_target, restarting postgresql,
> and then re-ANALYZE'ing a table should be sufficient (provided the
> columns have not had a statistics target explicitly set).
>

>>> What other diagnostics can I provide? This still doesn't answer the
>>> 40000 row question, though. It seems absurd to me that the planner
>>> would give up and just use 40000 rows (0.02 percent of the actual
>>> result).
>>>
>>
>> there can be some not well supported operation, then planner use a
>> some % from rows without statistic based estimation
>
> The strange thing is that the value 40000 keeps popping up in totally
> diffferent contexts, with different tables, databases, etc... I tried
> digging through the code and the only thing I found was that numGroups
> was being set to 40000 but I couldn't see where.
>


if I remember well, you can set a number of group by ALTER TABLE ALTER
COLUMN SET n_distinct = ..

maybe you use it.

Regards

Pavel Stehule

http://www.postgresql.org/docs/9.0/interactive/sql-altertable.html




> --
> Jon
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>



view thread (10+ messages)  latest in thread

Message-ID: <AANLkTin2eL+Xy8Gh5dOUTc6RH-ehyFu+eCcxHkVG4g4i@mail.gmail.com>
Permalink:  ../AANLkTin2eL+Xy8Gh5dOUTc6RH-ehyFu+eCcxHkVG4g4i@mail.gmail.com/
Also on:    postgresql.org/message-id/AANLkTin2eL+Xy8Gh5dOUTc6RH-ehyFu+eCcxHkVG4g4i@mail.gmail.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-performance@postgresql.org
  Cc: pavel.stehule@gmail.com, jnelson+pgsql@jamponi.net
  Subject: Re: postmaster consuming /lots/ of memory with hash aggregate. why?
  In-Reply-To: <AANLkTin2eL+Xy8Gh5dOUTc6RH-ehyFu+eCcxHkVG4g4i@mail.gmail.com>
  References: <AANLkTi=MOeC+D_1tUEACxt43Xv1x0EtHDjN23Hp5yuXW@mail.gmail.com> <AANLkTikgSPy+q0zRv5LLETa9NK7tP6GL9EThjk55-okP@mail.gmail.com> <AANLkTik9RA2YqQszZJZP90Wb0NbkhoMWJAz0gJoknPLi@mail.gmail.com> <AANLkTim6CnN1ubjnuRahV3p-UBaMUzD5nMWtQQ3batCE@mail.gmail.com> <AANLkTi=_igsVFkEg2H=90_aJpCrA2Q9FSFurTyLLu74m@mail.gmail.com> <AANLkTikv9BT3=5C1aXCJomej0gQxTkv3TX1DeiMjA-P-@mail.gmail.com> <AANLkTin2eL+Xy8Gh5dOUTc6RH-ehyFu+eCcxHkVG4g4i@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox