The following bug has been logged on the website:
Bug reference: 14811
Logged by: Oleg Serov
Email address: serovov@gmail.com
PostgreSQL version: 9.6.5
Operating system: Ubuntu
Description:
I have a query planner bug that executes the same subquery multiple times.
The query returns empty rows, but it takes 3 seconds to get the results. You
can re-write the query and get the results under 1 ms.
Here is how to reproduce it:
CREATE TABLE alpha ( id INTEGER PRIMARY KEY, important_data TEXT
);
INSERT INTO alpha SELECT i, random()::text FROM generate_series(1, 700000) AS i;
CREATE TABLE alpha2betta ( id SERIAL PRIMARY KEY, alpha_id INTEGER NOT NULL, betta_id INTEGER NOT NULL, FOREIGN
KEY(alpha_id)REFERENCES alpha(id), UNIQUE(alpha_id, betta_id)
);
INSERT INTO alpha2betta(alpha_id, betta_id) SELECT i, random()*100::integer FROM generate_series(1, 700000) AS
i;
CREATE TABLE betta2zetta ( id SERIAL PRIMARY KEY, betta_id INTEGER NOT NULL, zetta_id INTEGER NOT NULL,
UNIQUE(betta_id,zetta_id)
);
INSERT INTO betta2zetta(betta_id, zetta_id) SELECT random()*100::integer, i FROM generate_series(1, 300) AS i;
CREATE INDEX ON alpha2betta USING btree(alpha_id);
CREATE INDEX ON alpha2betta USING btree(betta_id);
CREATE INDEX ON betta2zetta USING btree(betta_id);
CREATE INDEX ON betta2zetta USING btree(zetta_id);
VACUUM FULL VERBOSE alpha;
VACUUM FULL VERBOSE alpha2betta;
VACUUM FULL VERBOSE betta2zetta;
SELECT 'Total runtime: 3644.929 ms:';
EXPLAIN ANALYZE
SELECT * FROM alpha WHERE alpha.id IN ( SELECT alpha2betta.alpha_id FROM alpha2betta WHERE
betta_idIN ( SELECT betta2zetta.betta_id FROM betta2zetta WHERE
zetta_id= 3001 ) )
LIMIT 6;
SELECT 'Total runtime: 0.060 ms:';
EXPLAIN ANALYZE
SELECT * FROM alpha WHERE alpha.id = ANY(ARRAY( SELECT alpha2betta.alpha_id FROM alpha2betta
WHERE betta_id IN ( SELECT betta2zetta.betta_id FROM betta2zetta
WHEREzetta_id = 3001 ) ))
LIMIT 6;
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
