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

> Thanks for the report, but I think your subject line is a little
> exaggerated.

Sorry for that :-)

> I'd not actually thought about expanding this to IN and NOT IN, but
> likely it would be possible, providing NULLs could be handled
> correctly too. Was this the optimisation you think is missing?

No - let's consider a real-world example where the problem becomes more
evident. Assume we have the following two tables:

CREATE TABLE book (id SERIAL NOT NULL, name VARCHAR, authorId SERIAL,
CONSTRAINT book_pkey PRIMARY KEY (id));

CREATE TABLE author (id SERIAL NOT NULL, surname VARCHAR, CONSTRAINT
author_pkey PRIMARY KEY (id));

All relevant columns are indexed. The book table contains 1.3 M rows.

Now let's say we want to find books by either name or by author name. A
simple problem, actually. The following query is used (which could of
course also be rewritten to use a JOIN instead, but that's not the point
here). We find out that it has an unexpected and unacceptably long
execution time:


EXPLAIN SELECT * FROM book WHERE name = 'Harry Potter' OR authorId IN
(SELECT id FROM author WHERE surname = 'Rowling');
                                         QUERY PLAN
-------------------------------------------------------------------------------------------
  Seq Scan on book  (cost=13.68..26792.88 rows=576709 width=40)
    Filter: (((name)::text = 'Harry Potter'::text) OR (hashed SubPlan 1))
    SubPlan 1
      ->  Bitmap Heap Scan on author  (cost=4.20..13.67 rows=6 width=4)
            Recheck Cond: ((surname)::text = 'Rowling'::text)
            ->  Bitmap Index Scan on author_surname_index
(cost=0.00..4.20 rows=6 width=0)
                  Index Cond: ((surname)::text = 'Rowling'::text)


In contrast to that, consider the query plan for the following logically
equivalent query, which executes in just a few milliseconds (!):

EXPLAIN SELECT * FROM book WHERE name = 'Harry Potter' UNION SELECT *
FROM book WHERE authorId IN (SELECT id FROM author WHERE surname =
'Rowling');
                                                  QUERY PLAN
------------------------------------------------------------------------------------------------------------
  HashAggregate  (cost=454.87..455.99 rows=112 width=29)
    Group Key: book.id, book.name, book.authorid
    ->  Append  (cost=0.43..454.03 rows=112 width=29)
          ->  Index Scan using book_name_index on book
(cost=0.43..16.48 rows=3 width=29)
                Index Cond: ((name)::text = 'Harry Potter'::text)
          ->  Nested Loop  (cost=4.63..436.43 rows=109 width=29)
                ->  Bitmap Heap Scan on author  (cost=4.20..13.67 rows=6
width=4)
                      Recheck Cond: ((surname)::text = 'Rowling'::text)
                      ->  Bitmap Index Scan on author_surname_index
(cost=0.00..4.20 rows=6 width=0)
                            Index Cond: ((surname)::text = 'Rowling'::text)
                ->  Index Scan using book_authorid_index on book book_1
(cost=0.43..70.28 rows=18 width=29)
                      Index Cond: (authorid = author.id)