Re: comparison of 8.3.10 to 8.3.14 reveals unexpected difference in explain plan

Mark Rostron wrote:

Was there any major optimizer change between 8.3.10 to 8.3.14? 

I’m getting a difference in explain plans that I need to account for.

There were some major changes in terms of how hashing is used for some types of query plans.  And one of the database parameters, default_statistics_target, increased from 10 to 100 between those two versions.  You can check what setting you have on each by doing:

show default_statistics_target;

From within psql.  It's possible the 8.3 optimizer is just getting lucky running without many statistics, and collecting more of them is making things worse.  It's also possible you're running into a situation where one of the new hash approaches in 8.4 just isn't working out well for you.

It would be easier to suggest what might be wrong if you included "EXPLAIN ANALYZE" output instead of just EXPLAIN.  It's not obvious whether 8.3 or 8.4 is estimating things better.

-- 
Greg Smith   2ndQuadrant US    greg@2ndQuadrant.com   Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support  www.2ndQuadrant.us
"PostgreSQL 9.0 High Performance": http://www.2ndQuadrant.com/books