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(at)2ndQuadrant(dot)com Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us
"PostgreSQL 9.0 High Performance": http://www.2ndQuadrant.com/books