[BUGS] BUG #14811: Nested IN SUBQERY that returns empty results executedmultiple times.

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