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@xxxxxxxxxxxxxxx
<mailto:ssinger@xxxxxxxxxxxxxxx>> 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
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
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
