pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedplanner weirdness: a join uses nestloop with checking condition when there are two subplan-or-hashed subqueries
3+ messages / 2 participants
[nested] [flat]
* planner weirdness: a join uses nestloop with checking condition when there are two subplan-or-hashed subqueries
@ 2020-02-25 14:32 Alexey Bashtanov <bashtanov@imap.cc>
2020-02-25 20:34 ` Re: planner weirdness: a join uses nestloop with checking condition when there are two subplan-or-hashed subqueries Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 3+ messages in thread
From: Alexey Bashtanov @ 2020-02-25 14:32 UTC (permalink / raw)
To: pgsql-bugs
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
^ permalink raw reply [nested|flat] 3+ messages in thread
2 further messages in this thread omitted from this page (render size limit).
Use the per-message pages, or the t.mbox.gz byte stream, for the complete thread.
end of thread, other threads:[~2020-05-22 21:08 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2020-02-25 14:32 planner weirdness: a join uses nestloop with checking condition when there are two subplan-or-hashed subqueries Alexey Bashtanov <bashtanov@imap.cc>
2020-02-25 20:34 ` Tom Lane <tgl@sss.pgh.pa.us>
2020-05-22 21:08 ` Alexey Bashtanov <bashtanov@imap.cc>
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