If you alter the default_statistics_target or any of the specific
statistics targets ( via ALTER TABLE SET STATISTICS ) , the change
will not have an effect until an analyze is performed.
This is implied by
but it might save questions like this if it were much more explicit.
On Wed, Oct 27, 2010 at 2:52 PM, Jon Nelson <jnelson+pgsql(at)jamponi(dot)net> wrote:
> On Wed, Oct 27, 2010 at 1:32 PM, Reid Thompson <Reid(dot)Thompson(at)ateb(dot)com> wrote:
>> On Wed, 2010-10-27 at 13:23 -0500, Jon Nelson wrote:
>>> set it to 500 and restarted postgres.
>> did you re-analyze?
> Not recently. I tried that, initially, and there was no improvement.
> I'll try it again now that I've set the stats to 500.
> The most recent experiment shows me that, unless I create whatever
> indexes I would like to see used *before* the large (first) update,
> then they just don't get used. At all. Why would I need to ANALYZE the
> table immediately following index creation? Isn't that part of the
> index creation process?
> Currently executing is a test where I place an "ANALYZE foo" after the
> COPY, first UPDATE, and first index, but before the other (much
> smaller) updates.
> Nope. The ANALYZE made no difference. This is what I just ran:
> CREATE TEMPORARY TABLE foo
> COPY ...
> UPDATE ... -- 1/3 of table, approx
> CREATE INDEX foo_rowB_idx on foo (rowB);
> ANALYZE ...
> -- queries from here to 'killed' use WHERE rowB = 'someval'
> UPDATE ... -- 7 rows. seq scan!
> UPDATE ... -- 242 rows, seq scan!
> UPDATE .. -- 3700 rows, seq scan!
> UPDATE .. -- 3100 rows, seq scan!
> Sent via pgsql-performance mailing list (pgsql-performance(at)postgresql(dot)org)
> To make changes to your subscription:
In response to
pgsql-performance by date
|Next:||From: Greg Smith||Date: 2010-10-27 19:24:25|
|Subject: Re: AIX slow buffer reads|
|Previous:||From: Scott Marlowe||Date: 2010-10-27 19:17:20|
|Subject: Re: CPUs for new databases|