Re: temporary tables, indexes, and query plans

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
http://www.postgresql.org/docs/9.0/static/planner-stats.html and
http://www.postgresql.org/docs/9.0/static/runtime-config-query.html#GUC-DEFAULT-STATISTICS-TARGET,
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:
>
> BEGIN;
> 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!
> killed.
>
>
> --
> Jon
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>