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

> Hmm... and this is even worse (on the data you provided):
>
> EXPLAIN (ANALYZE, BUFFERS)
> SELECT *
>   FROM book
>  WHERE name = 'Harry Potter'
>     OR EXISTS (
>        SELECT 1
>          FROM author
>         WHERE author.id = book.author AND author.name = 'Rowling'
>        );

Yes, but the problem seems to be even bigger. Apparently it's neither
limited to subqueries nor to the operators EXISTS, IN, NOT IN, ANY,
SOME, and ALL. It rather seems that the planner has a severe bug
regarding usage of the OR operator itself. This seems hard to believe,
so please verify the query plans given below (and also earlier). I'd be
happy if I'm mistaken on this.



EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM book JOIN author ON
(book.author = author.id) WHERE book.name = 'Harry Potter' OR
author.name = 'Rowling';
                                                        QUERY PLAN

------------------------------------------------------------------------------------------------------------------------
  Hash Join  (cost=309.00..4118.40 rows=11 width=78) (actual
time=325.283..325.283 rows=0 loops=1)
    Hash Cond: (book.author = author.id)
    Join Filter: (((book.name)::text = 'Harry Potter'::text) OR
((author.name)::text = 'Rowling'::text))
    Rows Removed by Join Filter: 100000
    Buffers: shared hit=1019
    ->  Seq Scan on book  (cost=0.00..1935.00 rows=100000 width=41)
(actual time=0.010..130.936 rows=100000 loops=1)
          Buffers: shared hit=935
    ->  Hash  (cost=184.00..184.00 rows=10000 width=37) (actual
time=28.933..28.933 rows=10000 loops=1)
          Buckets: 16384  Batches: 1  Memory Usage: 802kB
          Buffers: shared hit=84
          ->  Seq Scan on author  (cost=0.00..184.00 rows=10000
width=37) (actual time=0.007..14.061 rows=10000 loops=1)
                Buffers: shared hit=84
  Planning time: 0.456 ms
  Execution time: 325.546 ms



EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM book WHERE author IN (SELECT id
FROM author WHERE name = 'Rowling') OR FALSE;
                                                             QUERY PLAN

----------------------------------------------------------------------------------------------------------------------------------
  Seq Scan on book  (cost=8.30..2193.30 rows=50000 width=41) (actual
time=13.838..13.838 rows=0 loops=1)
    Filter: (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.032..0.032 rows=0 loops=1)
            Index Cond: ((name)::text = 'Rowling'::text)
            Buffers: shared hit=2
  Planning time: 0.204 ms
  Execution time: 13.910 ms