Re: Searching for the cause of a bad plan - Mailing list pgsql-performance

On Fri, 2007-09-21 at 12:03 +0200, Csaba Nagy wrote:
> prepare test_001(bigint) as
> SELECT tb.*
> FROM table_a ta
> JOIN table_b2 tb ON ta.b=tb.b
> WHERE ta.a = $1
> ORDER BY ta.a, ta.b
> limit 10;

Please re-run everything on clean tables without frigging the stats. We
need to be able to trust what is happening is normal.

Plan2 sees that b1 is wider, which will require more heap blocks to be
retrieved. It also sees b1 is less correlated than b2, so again will
require more database blocks to retrieve. Try increasing
effective_cache_size.

Can you plans with/without LIMIT and with/without cursor, for both b1
and b2?

--
  Simon Riggs
  2ndQuadrant  http://www.2ndQuadrant.com