BUG #14811: Nested IN SUBQERY that returns empty results executed multiple times.

The following bug has been logged on the website:

Bug reference: 14811
Logged by: Oleg Serov
Email address: serovov(at)gmail(dot)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_id IN (
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
WHERE zetta_id = 3001
)
))
LIMIT 6;