On Thu, Mar 30, 2017 at 12:30 AM, Robert Haas <robertmh...@gmail.com> wrote:
> I don't think the problem originates at the Merge Join, though,
> because the commit says that at is fixing semi and anti-join estimates
> - this is a plain inner join, so in theory it shouldn't change.
> However, it's a bit hard for me to piece through these plans, the
> formatting kind of got messed up - things are wrapped. Could you
> possibly attach the plans as attachments?
>
Sure, please find attached file for the plans before and after commit.
--
Regards,
Rafia Sabih
EnterpriseDB: http://www.enterprisedb.com/
The plan that completes in 19 hours after commit:
commit 7fa93eec4e0c9c3e801e3c51aa4bae3a38aaa218
Author: Tom Lane <t...@sss.pgh.pa.us>
Date: Sat Dec 17 15:28:54 2016 -0500
Fix FK-based join selectivity estimation for semi/antijoins.
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=60187550.59..60187550.59 rows=1 width=52) (actual
time=71064602.963..71064602.964 rows=1 loops=1)
-> Sort (cost=60187550.59..60187550.59 rows=3 width=52) (actual
time=71064602.961..71064602.961 rows=1 loops=1)
Sort Key: supplier.s_name
Sort Method: top-N heapsort Memory: 25kB
-> Nested Loop Semi Join (cost=52960550.15..60187550.57 rows=3
width=52) (actual time=2163639.699..71063153.953 rows=52263 loops=1)
Join Filter: (supplier.s_suppkey = lineitem.l_suppkey)
Rows Removed by Join Filter: 155706242106
-> Nested Loop (cost=963.43..10081.54 rows=120000 width=56)
(actual time=178.589..6282.852 rows=120358 loops=1)
-> Seq Scan on nation (cost=0.00..0.41 rows=1 width=4)
(actual time=0.018..0.042 rows=1 loops=1)
Filter: (n_name = 'EGYPT'::bpchar)
Rows Removed by Filter: 24
-> Bitmap Heap Scan on supplier (cost=963.43..8881.13
rows=120000 width=64) (actual time=178.563..6143.786 rows=120358 loops=1)
Recheck Cond: (s_nationkey = nation.n_nationkey)
Heap Blocks: exact=57317
-> Bitmap Index Scan on idx_supplier_nation_key
(cost=0.00..933.43 rows=120000 width=0) (actual time=133.218..133.218
rows=120358 loops=1)
Index Cond: (s_nationkey = nation.n_nationkey)
-> Materialize (cost=52959586.72..60024469.24 rows=85
width=16) (actual time=12.679..175.456 rows=1293693 loops=120358)
-> Merge Join (cost=52959586.72..60024468.82 rows=85
width=16) (actual time=1525322.753..2419045.641 rows=1696742 loops=1)
Merge Cond: ((lineitem.l_partkey =
partsupp.ps_partkey) AND (lineitem.l_suppkey = partsupp.ps_suppkey))
Join Filter: ((partsupp.ps_availqty)::numeric >
((0.5 * sum(lineitem.l_quantity))))
Rows Removed by Join Filter: 3771
-> GroupAggregate (cost=48448912.90..53325163.98
rows=144696357 width=48) (actual time=1342085.116..2178225.340 rows=156665300
loops=1)
Group Key: lineitem.l_partkey,
lineitem.l_suppkey
-> Sort (cost=48448912.90..49125364.33
rows=270580572 width=21) (actual time=1342085.072..1495863.254 rows=273057944
loops=1)
Sort Key: lineitem.l_partkey,
lineitem.l_suppkey
Sort Method: external merge Disk:
8282600kB
-> Bitmap Heap Scan on lineitem
(cost=2847641.84..10552097.42 rows=270580572 width=21) (actual
time=241170.637..650122.225 rows=273058377 loops=1)
Recheck Cond: ((l_shipdate >=
'1994-01-01'::date) AND (l_shipdate < '1995-01-01 00:00:00'::timestamp without
time zone))
Heap Blocks: exact=33444433
-> Bitmap Index Scan on
idx_l_shipdate (cost=0.00..2779996.70 rows=270580572 width=0) (actual
time=190321.155..190321.155 rows=273058377 loops=1)
Index Cond: ((l_shipdate >=
'1994-01-01'::date) AND (l_shipdate < '1995-01-01 00:00:00'::timestamp without
time zone))
-> Sort (cost=4510673.81..4516734.42 rows=2424244
width=24) (actual time=183237.183..184039.914 rows=2602656 loops=1)
Sort Key: partsupp.ps_partkey,
partsupp.ps_suppkey
Sort Method: quicksort Memory: 301637kB
-> Hash Join (cost=379620.36..4253593.60
rows=2424244 width=24) (actual time=8576.343..179703.563 rows=2602656 loops=1)
Hash Cond: (partsupp.ps_partkey =
part.p_partkey)
-> Seq Scan on partsupp
(cost=0.00..2949730.80 rows=240000000 width=20) (actual time=0.149..71902.279
rows=240000000 loops=1)
-> Hash (cost=372044.59..372044.59
rows=606062 width=4) (actual time=8574.476..8574.476 rows=650664 loops=1)
Buckets: 1048576 Batches: 1
Memory Usage: 31067kB
-> Gather
(cost=1000.00..372044.59 rows=606062 width=4) (actual time=0.677..8090.145
rows=650664 loops=1)
Workers Planned: 4
Workers Launched: 4
-> Parallel Seq Scan on
part (cost=0.00..310438.39 rows=151516 width=4) (actual time=0.056..8259.935
rows=130133 loops=5)
Filter:
((p_name)::text ~~ 'beige%'::text)
Rows Removed by
Filter: 11869867
Planning time: 4.669 ms
Execution time: 71065478.356 ms
The plan when aforementioned commit is reverted:
--------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------
Limit (cost=69766456.38..69766456.38 rows=1 width=52) (actual
time=6511846.141..6511846.142 rows=1 loops=1)
-> Sort (cost=69766456.38..69766606.38 rows=60000 width=52) (actual
time=6511846.140..6511846.140 rows=1 loops=1)
Sort Key: supplier.s_name
Sort Method: top-N heapsort Memory: 25kB
-> Nested Loop (cost=69742098.22..69766156.38 rows=60000 width=52)
(actual time=6492330.924..6511758.068 rows=52263 loops=1)
Join Filter: (supplier.s_nationkey = nation.n_nationkey)
Rows Removed by Join Filter: 1247849
-> Seq Scan on nation (cost=0.00..0.41 rows=1 width=4) (actual
time=0.010..0.040 rows=1 loops=1)
Filter: (n_name = 'EGYPT'::bpchar)
Rows Removed by Filter: 24
-> Nested Loop (cost=69742098.22..69747406.00 rows=1499997
width=60) (actual time=6492214.316..6511364.213 rows=1300112 loops=1)
-> HashAggregate (cost=69742097.79..69742182.18
rows=8439 width=16) (actual time=6492197.374..6493153.687 rows=1300126 loops=1)
Group Key: partsupp.ps_suppkey
-> Nested Loop Semi Join
(cost=48766000.84..69742076.69 rows=8439 width=16) (actual
time=2789938.293..6488159.843 rows=1696742 loops=1)
-> Merge Join (cost=48766000.27..69735727.56
rows=8439 width=32) (actual time=2789936.711..4549154.856 rows=156321526
loops=1)
Merge Cond: ((lineitem.l_partkey =
partsupp.ps_partkey) AND (lineitem.l_suppkey = partsupp.ps_suppkey))
Join Filter:
((partsupp.ps_availqty)::numeric > ((0.5 * sum(lineitem.l_quantity))))
Rows Removed by Join Filter: 344021
-> GroupAggregate
(cost=48765999.70..53642250.78 rows=144696357 width=48) (actual
time=2789923.013..3831748.957 rows=156665547 loops=1)
Group Key: lineitem.l_partkey,
lineitem.l_suppkey
-> Sort
(cost=48765999.70..49442451.13 rows=270580572 width=21) (actual
time=2789922.962..3082277.530 rows=273058377 loops=1)
Sort Key:
lineitem.l_partkey, lineitem.l_suppkey
Sort Method: external merge
Disk: 8282776kB
-> Bitmap Heap Scan on
lineitem (cost=2847641.84..10552097.42 rows=270580572 width=21) (actual
time=153718.541..2199397.396 rows=273058377 loops=1)
Recheck Cond:
((l_shipdate >= '1994-01-01'::date) AND (l_shipdate < '1995-01-01
00:00:00'::timestamp without time zone))
Rows Removed by Index
Recheck: 1346378434
Heap Blocks:
exact=669636 lossy=32774797
-> Bitmap Index Scan
on idx_l_shipdate (cost=0.00..2779996.70 rows=270580572 width=0) (actual
time=153028.968..153028.968 rows=273058377 loops=
1)
Index Cond:
((l_shipdate >= '1994-01-01'::date) AND (l_shipdate < '1995-01-01
00:00:00'::timestamp without time zone))
-> Index Scan using partsupp_pkey on
partsupp (cost=0.57..12722651.69 rows=240000000 width=20) (actual
time=13.682..201451.593 rows=240000000 loops=1)
-> Index Scan using part_pkey on part
(cost=0.56..0.75 rows=1 width=4) (actual time=0.012..0.012 rows=0
loops=156321526)
Index Cond: (p_partkey =
lineitem.l_partkey)
Filter: ((p_name)::text ~~
'beige%'::text)
Rows Removed by Filter: 1
-> Index Scan using supplier_pkey on supplier
(cost=0.43..0.61 rows=1 width=64) (actual time=0.012..0.013 rows=1
loops=1300126)
Index Cond: (s_suppkey = lineitem.l_suppkey)
Planning time: 2.588 ms
Execution time: 6512587.250 ms
(38 rows)
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
