Re: BUG #14107: Major query planner bug regarding subqueries and indices

> Queries return different number of rows, meaning they're not fully equivalent.

Well, I think in the given example they should actually be fully
equivalent, since the unique id column is selected as well. Thus, UNION
can't do any unwanted row eliminations.

> Well, then `EXPLAIN (analyze, buffers)` is also wanted, together with
> object definitions.

So here are the exact SQL commands to reproduce the problem.

-- Create table "book"

CREATE TABLE book (id SERIAL NOT NULL, name VARCHAR, author INTEGER,
CONSTRAINT book_pkey PRIMARY KEY (id));
CREATE INDEX book_name_index ON book (name);
CREATE INDEX book_author_index ON book (author);

-- Create table "author"

CREATE TABLE author (id SERIAL NOT NULL, name VARCHAR, CONSTRAINT
author_pkey PRIMARY KEY (id));
CREATE INDEX author_name_index ON author (name);

-- Insert some test data so that the planner would never assume a
sequential scan could be faster

INSERT INTO book (id, name, author) SELECT generate_series(1, 100000),
md5(random()::text), (generate_series(1, 100000) - 1) % 10000 + 1;
INSERT INTO author (id, name) SELECT generate_series(1, 10000),
md5(random()::text);
ANALYZE book;
ANALYZE author;

-- Check query plan when using OR operator

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM book WHERE name = 'Harry
Potter' OR author IN (SELECT id FROM author WHERE name = 'Rowling');
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------
Seq Scan on book (cost=8.30..2443.30 rows=50001 width=41) (actual
time=25.527..25.527 rows=0 loops=1)
Filter: (((name)::text = 'Harry Potter'::text) OR (hashed SubPlan 1))
Rows Removed by Filter: 100000
Buffers: shared hit=937
SubPlan 1
-> Index Scan using author_name_index on author (cost=0.29..8.30
rows=1 width=4) (actual time=0.041..0.041 rows=0 loops=1)
Index Cond: ((name)::text = 'Rowling'::text)
Buffers: shared hit=2
Planning time: 0.237 ms
Execution time: 25.603 ms

-- Check query plan when using UNION operator

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM book WHERE name = 'Harry
Potter' UNION SELECT * FROM book WHERE author IN (SELECT id FROM author
WHERE name = 'Rowling');
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------
HashAggregate (cost=58.42..58.53 rows=11 width=41) (actual
time=0.066..0.066 rows=0 loops=1)
Group Key: book.id, book.name, book.author
Buffers: shared hit=5
-> Append (cost=0.42..58.34 rows=11 width=41) (actual
time=0.061..0.061 rows=0 loops=1)
Buffers: shared hit=5
-> Index Scan using book_name_index on book (cost=0.42..8.44
rows=1 width=41) (actual time=0.035..0.035 rows=0 loops=1)
Index Cond: ((name)::text = 'Harry Potter'::text)
Buffers: shared hit=3
-> Nested Loop (cost=4.66..49.79 rows=10 width=41) (actual
time=0.019..0.019 rows=0 loops=1)
Buffers: shared hit=2
-> Index Scan using author_name_index on author
(cost=0.29..8.30 rows=1 width=4) (actual time=0.015..0.015 rows=0 loops=1)
Index Cond: ((name)::text = 'Rowling'::text)
Buffers: shared hit=2
-> Bitmap Heap Scan on book book_1 (cost=4.37..41.39
rows=10 width=41) (never executed)
Recheck Cond: (author = author.id)
-> Bitmap Index Scan on book_author_index
(cost=0.00..4.37 rows=10 width=0) (never executed)
Index Cond: (author = author.id)
Planning time: 0.669 ms
Execution time: 0.183 ms