pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedUnworkable plan above certain row count
2+ messages / 2 participants
[nested] [flat]
* Unworkable plan above certain row count
@ 2022-04-28 00:52 André Hänsel <andre@webkr.de>
0 siblings, 1 reply; 2+ messages in thread
From: André Hänsel @ 2022-04-28 00:52 UTC (permalink / raw)
To: pgsql-performance@lists.postgresql.org
I noticed an issue in a simple query with WHERE NOT IN (SELECT ...). I am
aware that anti-joins with NOT IN are currently not optimized and should be
rewritten as WHERE NOT EXISTS (SELECT ...), so if this is irrelevant please
just ignore it.
Here is a setup that works:
CREATE TABLE a
(
a_id serial NOT NULL,
PRIMARY KEY (a_id)
);
CREATE TABLE b
(
b_id serial NOT NULL,
a_id int NOT NULL,
PRIMARY KEY (b_id)
);
INSERT INTO a(a_id) SELECT generate_series(1, 20000);
INSERT INTO b(b_id, a_id) SELECT generate_series(1, 500000), floor(random()
* 22000 + 1)::int;
ANALYZE a;
ANALYZE b;
EXPLAIN SELECT count(*) FROM b WHERE a_id NOT IN (SELECT a_id FROM a);
Finalize Aggregate (cost=7596.23..7596.24 rows=1 width=8)
-> Gather (cost=7596.12..7596.23 rows=1 width=8)
Workers Planned: 1
-> Partial Aggregate (cost=6596.12..6596.13 rows=1 width=8)
-> Parallel Seq Scan on b (cost=339.00..6228.47 rows=147059
width=0)
Filter: (NOT (hashed SubPlan 1))
SubPlan 1
-> Seq Scan on a (cost=0.00..289.00 rows=20000
width=4)
Fiddle:
https://dbfiddle.uk/?rdbms=postgres_14&fiddle=497ab1d5eec6e02d4d1c0f6630b6f1
f1
Now if you change
INSERT INTO a(a_id) SELECT generate_series(1, 20000);
to
INSERT INTO a(a_id) SELECT generate_series(1, 200000);
i.e. add a zero, the plan becomes this:
Finalize Aggregate (cost=759860198.41..759860198.42 rows=1 width=8)
-> Gather (cost=759860198.29..759860198.40 rows=1 width=8)
Workers Planned: 1
-> Partial Aggregate (cost=759859198.29..759859198.30 rows=1
width=8)
-> Parallel Seq Scan on b (cost=0.00..759858830.65
rows=147059 width=0)
Filter: (NOT (SubPlan 1))
SubPlan 1
-> Materialize (cost=0.00..4667.00 rows=200000
width=4)
-> Seq Scan on a (cost=0.00..2885.00
rows=200000 width=4)
Fiddle:
https://dbfiddle.uk/?rdbms=postgres_14&fiddle=bec018196195635cb6ec05ccae3213
7c
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Unworkable plan above certain row count
@ 2022-04-28 02:08 Tom Lane <tgl@sss.pgh.pa.us>
parent: André Hänsel <andre@webkr.de>
0 siblings, 0 replies; 2+ messages in thread
From: Tom Lane @ 2022-04-28 02:08 UTC (permalink / raw)
To: André Hänsel <andre@webkr.de>; +Cc: pgsql-performance@lists.postgresql.org
=?iso-8859-1?Q?Andr=E9_H=E4nsel?= <andre@webkr.de> writes:
> Now if you change
> INSERT INTO a(a_id) SELECT generate_series(1, 20000);
> to
> INSERT INTO a(a_id) SELECT generate_series(1, 200000);
> i.e. add a zero, the plan becomes [ not a hashed subplan ]
Yeah, it won't hash the subplan if the estimated size of the hash
table exceeds work_mem. In this case, boosting work_mem would be
a mighty good idea.
regards, tom lane
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2022-04-28 02:08 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2022-04-28 00:52 Unworkable plan above certain row count André Hänsel <andre@webkr.de>
2022-04-28 02:08 ` Tom Lane <tgl@sss.pgh.pa.us>
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