Re: BUG #14107: Major query planner bug regarding subqueries and indices - Mailing list pgsql-bugs

> 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