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

From Mathias Kunter
Subject Re: Re: BUG #14107: Major query planner bug regarding subqueries and indices
Date
Msg-id 3ac6f379-08f8-d1db-639f-2dc3075d2eb4@gmail.com
Whole thread Raw
In response to Re: BUG #14107: Major query planner bug regarding subqueries and indices  (Yaroslav <ladayaroslav@yandex.ru>)
Responses Re: BUG #14107: Major query planner bug regarding subqueries and indices
List pgsql-bugs
> 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

pgsql-bugs by date:

Previous
From: Yaroslav
Date:
Subject: Re: BUG #14107: Major query planner bug regarding subqueries and indices
Next
From: Tom Lane
Date:
Subject: Re: UPDATE grabs multiple rows when it seems like it should only grab one