On Wed, Feb 2, 2011 at 1:19 PM, Jon Nelson <jnelson+pgsql@xxxxxxxxxxx> wrote: > However, sometimes using an index results in a HORRIBLE HORRIBLE plan. > I recently encountered the issue myself, and plopping an ANALYZE > $tablename in there, since I was using a temporary table anyway, make > all the difference. The planner switched from an index-based query to > a sequential scan, and a sequential scan was (is) vastly more > efficient in this particular case. Yep... > Personally, I'd get rid of autovacuum/autoanalyze support on temporary > tables We don't have any such support, which I think is the root of Mladen's complaint. > (they typically have short lives and are often accessed > immediately after creation preventing the auto* stuff from being > useful anyway), *AND* every time I ask I'm always told "make sure > ANALYZE the table before you use it". Yeah. Any kind of bulk load into an empty table can be a problem, even if it's not temporary. When you load a bunch of data and then immediately plan a query against it, autoanalyze hasn't had a chance to do its thing yet, so sometimes you get a lousy plan. In the case of temporary tables, this can happen even if there's a delay before you use the data. Some sort of fix for this - where the first query that needs the stats does an analyze first - seems like it could be quite useful (although it would suck if the transaction that took it upon itself to do the analyze then rolled back, losing the stats and forcing the next guy to do it all over again). -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
