Date: Wed, 10 Apr 2013 08:56:57 -0500
From: "ktm@rice.edu" <ktm@rice.edu>
To: Steve Singer <ssinger@ca.afilias.info>
Cc: pgsql-performance@postgresql.org
Subject: Re: slow bitmap heap scans on pg 9.2
Message-ID: <20130410135657.GY32580@aart.rice.edu>
References: <51656E03.7000701@ca.afilias.info>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <51656E03.7000701@ca.afilias.info>
User-Agent: Mutt/1.5.20 (2009-12-10)
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

On Wed, Apr 10, 2013 at 09:49:55AM -0400, Steve Singer wrote:
> I'm encountering an issue where PG 9.2.4 (we also see this with
> 9.2.3) is picking a plan involving a bitmap heap scan that turns out
> to be much slower than a nested-loop plan using indexes.
> 
> The planner picks the hashjoin plan by default (see attached files)
> 
> Bitmap Heap Scan on public.table_b_2 b  (cost=172635.99..9800225.75
> rows=8435754 width=10) (actual t
> ime=9132.194..1785196.352 rows=9749680 loops=1)
>                            Recheck Cond: ((b.organization_id = 3)
> AND (b.year = 2013) AND (b.month = 3))
>                            Rows Removed by Index Recheck: 313195667
>                            Filter: (b.product_id = 2)
> 
> Is the part that seems be causing the problem (or at least taking
> most of the time, other than the final aggregation)
> 
> If I set enable_hashjoin=false and enable_mergejoin=false I get the
> nestedloop join plan.
> 
> table_b is 137 GB plus indexes each on is around 43 GB
> table_a is 20 GB
> 
> random_page_cost = 2.0
> effective_cache_size = 3500MB
> cpu_tuple_cost = 0.01
> cpu_index_tuple_cost = 0.005
> cpu_operator_cost = 0.0025
> work_mem = 64MB
> shared_buffers = 300MB  (for this output, I've also had it at 2GB)
> 
> If I bump cpu_tuple_cost to the 10-20 range it will pick the nested
> loop join for some date ranges but not all. cpu_tuple_cost of 20
> doesn't sound like an sane value.
> 
> This database used to run 8.3 where it picked the nested-loop join.
> We used pg_upgrade to migrate to 9.2
> 
> Any ideas why the bitmap heap scan is much slower than the planner expects?
> 
> Steve

Hi Steve,

The one thing that stands out to me is that you are working with 200GB of
data on a machine with 4-8GB of ram and you have the random_page_cost set
to 2.0. That is almost completely uncached and I would expect a value of
10 or more to be closer to reality.

Regards,
Ken


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance