From: Steve Singer <ssinger@ca.afilias.info>
To: Jeff Janes <jeff.janes@gmail.com>
Cc: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: slow bitmap heap scans on pg 9.2
Date: Thu, 11 Apr 2013 10:20:11 -0400
Message-ID: <5166C69B.1010505@ca.afilias.info> (raw)
In-Reply-To: <5165FBA1.90704@ca.afilias.info>
References: <51656E03.7000701@ca.afilias.info>
<CAMkU=1xQXQWBw8AngvhM70K9GOGby_WAGTR3YTDZ26apv9k9pw@mail.gmail.com>
<5165FBA1.90704@ca.afilias.info>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
On 13-04-10 07:54 PM, Steve Singer wrote:
> On 13-04-10 02:06 PM, Jeff Janes wrote:
>> On Wed, Apr 10, 2013 at 6:49 AM, Steve Singer <ssinger@ca.afilias.info
>> <mailto:ssinger@ca.afilias.info>> wrote:
>>
>
>> I think the index recheck means your bitmap is overflowing (i.e. needing
>> more space than work_mem) and so keeping only the pages which have at
>> least one match, which means all rows in those pages need to be
>> rechecked. How many rows does the table have? You might be essentially
>> doing a seq scan, but with the additional overhead of the bitmap
>> machinery. Could you do "explain (analyze,buffers)", preferably with
>> track_io_timing set to on?
>>
>
> table_b has 1,530,710,469 rows
>
> Attached is the output with track_io_timings and buffers.
>
I've done some more testing with a random_page_cost=20.
This gives me the nested-loop plan for the various date ranges I've tried.
However table_a_2 and table_b_2 are actually partition tables. This
query only needs to look at a single partition. When I run this same
query against a different partition (a smaller partition, but still
bigger than cache) it picks hash join plan involving a seq scan of
table_b but no bitmap index scan. On this partition the hash-join
plans tend to take 15 minutes versus 2 minutes when I disable hashjoin
plans. Bumping random_page_cost higher doesn't fix this.
I think the reason why it is picking the hash join based plans is
because of
Index Scan using table_b_1_ptid_orgid_ym_unq on table_b_1 b
(cost=0.00..503.86 rows=1 width=10) (actual time=0.016..0.017 rows=1
loops=414249)
Index Cond: ((a.id = a_id) AND (organization_id =
2) AND (year = 2013) AND (month = 3))
Filter: (product_id = 1)
I think we are over-estimating the cost of the index scans in the inner
loop. This seems similar to what was discussed a few months ago
http://www.postgresql.org/message-id/092a01cdd230$ff6143c0$fe23cb40$@foo.me.uk
This version of PG should have 3e9960e9d935e7e applied. I am trying to
get the database copied to a machine where I can easily switch PG
versions and test this against something prior to that commit and also
against a 9.3 build.
Steve
>
>
>> Cheers,
>>
>> Jeff
>
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performanceAttachments:nestedloop_1.txt (text/plain, 3.6K) download
reset enable_hashjoin=false;
set enable_mergejoin=false;
set random_page_cost=20;
HashAggregate (cost=126233158.01..126233163.21 rows=347 width=53) (actual time=82471.963..82471.983 rows=26 loops=1)
-> Nested Loop (cost=0.00..125974847.48 rows=248974 width=53) (actual time=473.558..39846.799 rows=414249 loops=1)
Join Filter: (a.product_operation = o.id)
Rows Removed by Join Filter: 16569960
-> Nested Loop Left Join (cost=0.00..125821726.96 rows=248974 width=42) (actual time=473.500..35090.816 rows=414249 loops=1)
-> Index Scan using trans_posted_1_trans_date_idx on table_a_1 a (cost=0.00..371973.35 rows=248974 width=36) (actual time=81.080..27242.825 rows=414249 loops=1)
Index Cond: ((transaction_date >= '2013-01-01 00:00:00+00'::timestamp with time zone) AND (transaction_date < '2013-02-01 00:00:00+00'::timestamp with time zone))
Filter: ((twin_id IS NULL) AND (product_id = 1) AND ((trans_type_id = ANY ('{2,3,4}'::integer[])) OR (trans_type_id IS NULL)))
Rows Removed by Filter: 546641
-> Index Scan using table_b_1_ptid_orgid_ym_unq on table_b_1 b (cost=0.00..503.86 rows=1 width=10) (actual time=0.016..0.017 rows=1 loops=414249)
Index Cond: ((a.id = a_id) AND (organization_id = 2) AND (year = 2013) AND (month = 3))
Filter: (product_id = 1)
-> Materialize (cost=0.00..1.62 rows=41 width=19) (actual time=0.000..0.004 rows=41 loops=414249)
-> Seq Scan on product_operations o (cost=0.00..1.41 rows=41 width=19) (actual time=0.026..0.036 rows=41 loops=1)
Total runtime: 82472.294 ms
(15 rows)
set enable_hashjoin=true;
set enable_mergejoin=true;
set random_page_cost=20;
HashAggregate (cost=19998189.54..19998194.75 rows=347 width=53) (actual time=825795.403..825795.420 rows=26 loops=1)
-> Nested Loop (cost=375085.52..19739879.02 rows=248974 width=53) (actual time=727075.300..782232.417 rows=414249 loops=1)
Join Filter: (a.product_operation = o.id)
Rows Removed by Join Filter: 16569960
-> Hash Right Join (cost=375085.52..19586758.50 rows=248974 width=42) (actual time=727075.034..777530.559 rows=414249 loops=1)
Hash Cond: (b.a_id = a.id)
-> Seq Scan on table_b_1 b (cost=0.00..19153996.20 rows=4605486 width=10) (actual time=193813.645..733574.903 rows=6416840 loops=1)
Filter: ((product_id = 1) AND (organization_id = 2) AND (year = 2013) AND (month = 3))
Rows Removed by Filter: 596869132
-> Hash (cost=371973.35..371973.35 rows=248974 width=36) (actual time=23994.832..23994.832 rows=414249 loops=1)
Buckets: 32768 Batches: 1 Memory Usage: 27509kB
-> Index Scan using table_a_1_trans_date_idx on table_a_1 a (cost=0.00..371973.35 rows=248974 width=36) (actual time=1.051..23615.761 rows=414249 loops=1)
Index Cond: ((transaction_date >= '2013-01-01 00:00:00+00'::timestamp with time zone) AND (transaction_date < '2013-02-01 00:00:00+00'::timestamp with time zone))
Filter: ((twin_id IS NULL) AND (product_id = 1) AND ((trans_type_id = ANY ('{2,3,4}'::integer[])) OR (trans_type_id IS NULL)))
Rows Removed by Filter: 546641
-> Materialize (cost=0.00..1.62 rows=41 width=19) (actual time=0.000..0.004 rows=41 loops=414249)
-> Seq Scan on product_operations o (cost=0.00..1.41 rows=41 width=19) (actual time=0.175..0.189 rows=41 loops=1)
Total runtime: 825802.496 ms
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: ssinger@ca.afilias.info, jeff.janes@gmail.com
Subject: Re: slow bitmap heap scans on pg 9.2
In-Reply-To: <5166C69B.1010505@ca.afilias.info>
* 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