On Wed, Feb 2, 2011 at 12:11 PM, Mladen Gogala <mladen.gogala@xxxxxxxxxxx> wrote: > Robert Haas wrote: >> >> On Tue, Feb 1, 2011 It would be pretty hard to make autoanalyze work on >> such tables >> without removing some of the performance benefits of having such >> tables in the first place - namely, the local buffer manager. ÂBut you >> could ANALYZE them by hand. >> >> > > Not necessarily autoanalyze, some default rules for the situations when > stats is not there should be put in place, > like the following: > 1) If there is a usable index on the temp table - use it. > 2) It there isn't a usable index on the temp table and there is a join, make > the temp table the first table > Â in the nested loop join. > > People are complaining about the optimizer not using the indexes all over > the place, there should be a way to > make the optimizer explicitly prefer the indexes, like was the case with > Oracle's venerable RBO (rules based > optimizer). RBO didn't use statistics, it had a rank of access method and > used the access method with the highest > rank of all available access methods. In practice, it translated into: if an > index exists - use it. 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. Personally, I'd get rid of autovacuum/autoanalyze support on temporary tables (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". -- Jon -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
