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+pg...@jamponi.net> wrote: > On Wed, Oct 27, 2010 at 1:32 PM, Reid Thompson <reid.thomp...@ateb.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@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-performance > -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance