pg.ddx.io pgsql-bugs@postgresql.org mailing list archive help / color / mirror / Atom feed From: Alexey Bashtanov <bashtanov@imap.cc>
To: pgsql-bugs@postgresql.org
Subject: planner weirdness: a join uses nestloop with checking condition when there are two subplan-or-hashed subqueries
Date: Tue, 25 Feb 2020 14:32:17 +0000
Message-ID: <ff42b25b-ff03-27f8-ed11-b8255d658cd5@imap.cc> (raw )
Hello,
Planner seems to make a weird choice of join algorithm -- O(M*N)
nestloop -- under certain circumstances.
I'm not exactly sure what is the criteria but I have a self-contained
example, albeit a large one.
So if you unpack the pb-dump.sql.bz2 attached and run the pb-test.sql
you'll have some plan explained.
Despite an index present on "qux" it's accessed using a plain seq-seq
nestloop, see pb-plan.txt
Why? Cardinalities look like predicted reasonably well, and with those
predictions hash join or index scan would
be obviously faster: the planner thinks we are joining 149 and 175728 rows.
The distribution for "qux"."foo_id" is not too skew, the average number
of rows per "foo_id" in "qux" is about 9.
Slight data or query variations make it use the index.
With "set enable_nestloop to off; set enable_mergejoin to off;" the plan
generated is better. It has smaller cost of the final join, though the
costs for the outer relation increase, probably due to the "never
executed" path.
Playing with json/from_collapse_limit does not make any difference.
I can observe this on both master and v 10.11 .
I haven't investigated it any further yet, so for now just asking
whether it's a known behavior?
If not, I'll try to find out what's going on.
Best, Alex
Nested Loop Left Join (cost=0.00..53543550.45 rows=1135 width=8) (actual time=714.969..1407.137 rows=1051 loops=1)
Join Filter: (qux.foo_id = foo.id)
Rows Removed by Join Filter: 3689237
-> Seq Scan on foo (cost=0.00..53045460.77 rows=149 width=4) (actual time=711.364..714.312 rows=21 loops=1)
Filter: (enabled AND (tags ~~ '%foo%'::text) AND ((alternatives: SubPlan 1 or hashed SubPlan 2) OR (alternatives: SubPlan 3 or hashed SubPlan 4)))
Rows Removed by Filter: 23050
SubPlan 1
-> Nested Loop (cost=0.84..4595.88 rows=4 width=0) (never executed)
-> Nested Loop (cost=0.42..4522.44 rows=166 width=4) (never executed)
-> Seq Scan on bar (cost=0.00..3092.22 rows=29 width=4) (never executed)
Filter: (foo_id = foo.id)
-> Index Scan using baz_bar_id_idx on baz (cost=0.42..48.84 rows=48 width=8) (never executed)
Index Cond: (bar_id = bar.id)
-> Index Scan using baz_details_pkey on baz_details bazd (cost=0.42..0.44 rows=1 width=4) (never executed)
Index Cond: (id = baz.id)
Filter: is_cool
SubPlan 2
-> Hash Join (cost=8576.65..27448.60 rows=26651 width=4) (actual time=93.642..356.957 rows=13374 loops=1)
Hash Cond: (baz_1.bar_id = bar_1.id)
-> Hash Join (cost=2943.85..20821.84 rows=26651 width=4) (actual time=26.860..273.359 rows=13374 loops=1)
Hash Cond: (baz_1.id = bazd_1.id)
-> Seq Scan on baz baz_1 (cost=0.00..15125.53 rows=1048553 width=8) (actual time=0.017..113.366 rows=1048553 loops=1)
-> Hash (cost=2610.71..2610.71 rows=26651 width=4) (actual time=26.800..26.800 rows=26727 loops=1)
Buckets: 32768 Batches: 1 Memory Usage: 1196kB
-> Seq Scan on baz_details bazd_1 (cost=0.00..2610.71 rows=26651 width=4) (actual time=0.017..22.757 rows=26727 loops=1)
Filter: is_cool
Rows Removed by Filter: 154244
-> Hash (cost=2635.58..2635.58 rows=182658 width=8) (actual time=66.619..66.619 rows=182658 loops=1)
Buckets: 131072 Batches: 4 Memory Usage: 2820kB
-> Seq Scan on bar bar_1 (cost=0.00..2635.58 rows=182658 width=8) (actual time=0.023..29.878 rows=182658 loops=1)
SubPlan 3
-> Nested Loop (cost=0.84..4595.88 rows=4 width=0) (never executed)
-> Nested Loop (cost=0.42..4522.44 rows=166 width=4) (never executed)
-> Seq Scan on bar bar_2 (cost=0.00..3092.22 rows=29 width=4) (never executed)
Filter: (foo_id = foo.id)
-> Index Scan using baz_bar_id_idx on baz baz_2 (cost=0.42..48.84 rows=48 width=8) (never executed)
Index Cond: (bar_id = bar_2.id)
-> Index Scan using baz_details_pkey on baz_details bazd_2 (cost=0.42..0.44 rows=1 width=4) (never executed)
Index Cond: (id = baz_2.id)
Filter: (is_cool IS NOT FALSE)
SubPlan 4
-> Hash Join (cost=8576.65..27448.60 rows=26651 width=4) (actual time=84.746..348.771 rows=13374 loops=1)
Hash Cond: (baz_3.bar_id = bar_3.id)
-> Hash Join (cost=2943.85..20821.84 rows=26651 width=4) (actual time=26.076..272.926 rows=13374 loops=1)
Hash Cond: (baz_3.id = bazd_3.id)
-> Seq Scan on baz baz_3 (cost=0.00..15125.53 rows=1048553 width=8) (actual time=0.017..114.680 rows=1048553 loops=1)
-> Hash (cost=2610.71..2610.71 rows=26651 width=4) (actual time=26.016..26.016 rows=26727 loops=1)
Buckets: 32768 Batches: 1 Memory Usage: 1196kB
-> Seq Scan on baz_details bazd_3 (cost=0.00..2610.71 rows=26651 width=4) (actual time=0.017..22.370 rows=26727 loops=1)
Filter: (is_cool IS NOT FALSE)
Rows Removed by Filter: 154244
-> Hash (cost=2635.58..2635.58 rows=182658 width=8) (actual time=58.589..58.589 rows=182658 loops=1)
Buckets: 131072 Batches: 4 Memory Usage: 2820kB
-> Seq Scan on bar bar_3 (cost=0.00..2635.58 rows=182658 width=8) (actual time=0.020..26.063 rows=182658 loops=1)
-> Materialize (cost=0.00..4100.92 rows=175728 width=8) (actual time=0.003..19.522 rows=175728 loops=21)
-> Seq Scan on qux (cost=0.00..2535.28 rows=175728 width=8) (actual time=0.017..19.754 rows=175728 loops=1)
Attachments:
[text/plain] pb-plan.txt (4.8K, ../ff42b25b-ff03-27f8-ed11-b8255d658cd5@imap.cc/2-pb-plan.txt)
download | inline:
Nested Loop Left Join (cost=0.00..53543550.45 rows=1135 width=8) (actual time=714.969..1407.137 rows=1051 loops=1)
Join Filter: (qux.foo_id = foo.id)
Rows Removed by Join Filter: 3689237
-> Seq Scan on foo (cost=0.00..53045460.77 rows=149 width=4) (actual time=711.364..714.312 rows=21 loops=1)
Filter: (enabled AND (tags ~~ '%foo%'::text) AND ((alternatives: SubPlan 1 or hashed SubPlan 2) OR (alternatives: SubPlan 3 or hashed SubPlan 4)))
Rows Removed by Filter: 23050
SubPlan 1
-> Nested Loop (cost=0.84..4595.88 rows=4 width=0) (never executed)
-> Nested Loop (cost=0.42..4522.44 rows=166 width=4) (never executed)
-> Seq Scan on bar (cost=0.00..3092.22 rows=29 width=4) (never executed)
Filter: (foo_id = foo.id)
-> Index Scan using baz_bar_id_idx on baz (cost=0.42..48.84 rows=48 width=8) (never executed)
Index Cond: (bar_id = bar.id)
-> Index Scan using baz_details_pkey on baz_details bazd (cost=0.42..0.44 rows=1 width=4) (never executed)
Index Cond: (id = baz.id)
Filter: is_cool
SubPlan 2
-> Hash Join (cost=8576.65..27448.60 rows=26651 width=4) (actual time=93.642..356.957 rows=13374 loops=1)
Hash Cond: (baz_1.bar_id = bar_1.id)
-> Hash Join (cost=2943.85..20821.84 rows=26651 width=4) (actual time=26.860..273.359 rows=13374 loops=1)
Hash Cond: (baz_1.id = bazd_1.id)
-> Seq Scan on baz baz_1 (cost=0.00..15125.53 rows=1048553 width=8) (actual time=0.017..113.366 rows=1048553 loops=1)
-> Hash (cost=2610.71..2610.71 rows=26651 width=4) (actual time=26.800..26.800 rows=26727 loops=1)
Buckets: 32768 Batches: 1 Memory Usage: 1196kB
-> Seq Scan on baz_details bazd_1 (cost=0.00..2610.71 rows=26651 width=4) (actual time=0.017..22.757 rows=26727 loops=1)
Filter: is_cool
Rows Removed by Filter: 154244
-> Hash (cost=2635.58..2635.58 rows=182658 width=8) (actual time=66.619..66.619 rows=182658 loops=1)
Buckets: 131072 Batches: 4 Memory Usage: 2820kB
-> Seq Scan on bar bar_1 (cost=0.00..2635.58 rows=182658 width=8) (actual time=0.023..29.878 rows=182658 loops=1)
SubPlan 3
-> Nested Loop (cost=0.84..4595.88 rows=4 width=0) (never executed)
-> Nested Loop (cost=0.42..4522.44 rows=166 width=4) (never executed)
-> Seq Scan on bar bar_2 (cost=0.00..3092.22 rows=29 width=4) (never executed)
Filter: (foo_id = foo.id)
-> Index Scan using baz_bar_id_idx on baz baz_2 (cost=0.42..48.84 rows=48 width=8) (never executed)
Index Cond: (bar_id = bar_2.id)
-> Index Scan using baz_details_pkey on baz_details bazd_2 (cost=0.42..0.44 rows=1 width=4) (never executed)
Index Cond: (id = baz_2.id)
Filter: (is_cool IS NOT FALSE)
SubPlan 4
-> Hash Join (cost=8576.65..27448.60 rows=26651 width=4) (actual time=84.746..348.771 rows=13374 loops=1)
Hash Cond: (baz_3.bar_id = bar_3.id)
-> Hash Join (cost=2943.85..20821.84 rows=26651 width=4) (actual time=26.076..272.926 rows=13374 loops=1)
Hash Cond: (baz_3.id = bazd_3.id)
-> Seq Scan on baz baz_3 (cost=0.00..15125.53 rows=1048553 width=8) (actual time=0.017..114.680 rows=1048553 loops=1)
-> Hash (cost=2610.71..2610.71 rows=26651 width=4) (actual time=26.016..26.016 rows=26727 loops=1)
Buckets: 32768 Batches: 1 Memory Usage: 1196kB
-> Seq Scan on baz_details bazd_3 (cost=0.00..2610.71 rows=26651 width=4) (actual time=0.017..22.370 rows=26727 loops=1)
Filter: (is_cool IS NOT FALSE)
Rows Removed by Filter: 154244
-> Hash (cost=2635.58..2635.58 rows=182658 width=8) (actual time=58.589..58.589 rows=182658 loops=1)
Buckets: 131072 Batches: 4 Memory Usage: 2820kB
-> Seq Scan on bar bar_3 (cost=0.00..2635.58 rows=182658 width=8) (actual time=0.020..26.063 rows=182658 loops=1)
-> Materialize (cost=0.00..4100.92 rows=175728 width=8) (actual time=0.003..19.522 rows=175728 loops=21)
-> Seq Scan on qux (cost=0.00..2535.28 rows=175728 width=8) (actual time=0.017..19.754 rows=175728 loops=1)
[application/sql] pb-test.sql (1.6K, ../ff42b25b-ff03-27f8-ed11-b8255d658cd5@imap.cc/3-pb-test.sql)
download
[application/x-bzip] pb-dump.sql.bz2 (6.8M, ../ff42b25b-ff03-27f8-ed11-b8255d658cd5@imap.cc/4-pb-dump.sql.bz2)
download
view thread (3+ messages) latest in thread
Message-ID: <ff42b25b-ff03-27f8-ed11-b8255d658cd5@imap.cc>
Permalink: ../ff42b25b-ff03-27f8-ed11-b8255d658cd5@imap.cc/
Also on: postgresql.org/message-id/ff42b25b-ff03-27f8-ed11-b8255d658cd5@imap.cc
copy link · copy postgr.es
reply 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-bugs@postgresql.org
Cc: bashtanov@imap.cc
Subject: Re: planner weirdness: a join uses nestloop with checking condition when there are two subplan-or-hashed subqueries
In-Reply-To: <ff42b25b-ff03-27f8-ed11-b8255d658cd5@imap.cc>
* 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