The following bug has been logged on the website:
Bug reference: 17964
Logged by: Mathias Kunter
Email address: mathiaskunter@gmail.com
PostgreSQL version: 14.8
Operating system: x86_64-pc-linux-gnu
Description:
In the example below, the query planner uses a sequential scan (query 1)
even though it could use an index scan (query 2).
CREATE TABLE table1 (id SERIAL NOT NULL, name VARCHAR, CONSTRAINT
table1_pkey PRIMARY KEY (id));
CREATE TABLE table2 (id SERIAL NOT NULL, name VARCHAR, CONSTRAINT
table2_pkey PRIMARY KEY (id));
CREATE TABLE table3 (id INTEGER NOT NULL);
INSERT INTO table1 (id, name) SELECT generate_series(1, 10000),
md5(random()::text);
INSERT INTO table2 (id, name) SELECT generate_series(1, 10000),
md5(random()::text);
INSERT INTO table3 (id) VALUES (1538),(8836),(5486),(3464),(2673);
ANALYZE;
EXPLAIN ANALYZE SELECT id, name FROM (SELECT id, name FROM table1 UNION
SELECT id, name FROM table2) AS q
WHERE id IN (SELECT id FROM table3);
---------------------------------------------------------------
Hash Semi Join (cost=769.11..1227.17 rows=500 width=36) (actual
time=10.952..15.094 rows=10 loops=1)
Hash Cond: (table1.id = table3.id)
-> HashAggregate (cost=768.00..968.00 rows=20000 width=36) (actual
time=10.757..13.597 rows=20000 loops=1)
Group Key: table1.id, table1.name
Batches: 1 Memory Usage: 2577kB
-> Append (cost=0.00..668.00 rows=20000 width=36) (actual
time=0.010..4.135 rows=20000 loops=1)
-> Seq Scan on table1 (cost=0.00..184.00 rows=10000
width=37) (actual time=0.009..1.288 rows=10000 loops=1)
-> Seq Scan on table2 (cost=0.00..184.00 rows=10000
width=37) (actual time=0.010..1.336 rows=10000 loops=1)
-> Hash (cost=1.05..1.05 rows=5 width=4) (actual time=0.014..0.015
rows=5 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Seq Scan on table3 (cost=0.00..1.05 rows=5 width=4) (actual
time=0.009..0.010 rows=5 loops=1)
Planning Time: 0.444 ms
Execution Time: 15.257 ms
EXPLAIN ANALYZE SELECT id, name FROM (SELECT id, name FROM table1 UNION
SELECT id, name FROM table2) AS q
WHERE id IN (1538,8836,5486,3464,2673);
---------------------------------------------------------------
HashAggregate (cost=51.23..51.33 rows=10 width=36) (actual
time=0.065..0.067 rows=10 loops=1)
Group Key: table1.id, table1.name
Batches: 1 Memory Usage: 24kB
-> Append (cost=0.29..51.18 rows=10 width=36) (actual time=0.025..0.057
rows=10 loops=1)
-> Index Scan using table1_pkey on table1 (cost=0.29..25.51 rows=5
width=37) (actual time=0.024..0.038 rows=5 loops=1)
Index Cond: (id = ANY
('{1538,8836,5486,3464,2673}'::integer[]))
-> Index Scan using table2_pkey on table2 (cost=0.29..25.51 rows=5
width=37) (actual time=0.006..0.017 rows=5 loops=1)
Index Cond: (id = ANY
('{1538,8836,5486,3464,2673}'::integer[]))
Planning Time: 0.170 ms
Execution Time: 0.097 ms
The results are also provided as SQL fiddles here:
https://dbfiddle.uk/xo7fug1o
https://www.db-fiddle.com/f/iUsfpdP2eD8YtdN2Em7Zyu/0