From: Simon Riggs <simon@2ndquadrant.com>
To: Csaba Nagy <nagy@ecircle-ag.com>
Cc: postgres performance list <pgsql-performance@postgresql.org>
Subject: Re: Searching for the cause of a bad plan
Date: Fri, 21 Sep 2007 11:59:25 +0100
Message-ID: <1190372365.4202.14.camel@ebony.site> (raw)
In-Reply-To: <1190369024.4661.173.camel@PCD12478>
References: <1190369024.4661.173.camel@PCD12478>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: simon@2ndquadrant.com, nagy@ecircle-ag.com
Subject: Re: Searching for the cause of a bad plan
In-Reply-To: <1190372365.4202.14.camel@ebony.site>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox