Re: LEFT OUTER JOIN and WHERE madness (8.3.3 bug?)

toruvinn <toruvinn@lain.pl> writes:
> toruvinn=> EXPLAIN ANALYZE SELECT
> i.id, i.albumid, i.userid,
> a.id as aid, a.visible_for AS al_visible_for, i.visible_for
>  FROM items i
> LEFT OUTER JOIN albums a ON a.id=i.albumid
> WHERE i.userid=564667
> AND ((a.id IS NULL AND (i.visible_for IN (0,1))) OR a.visible_for IN (0,1))
> AND i.type=1
> ORDER BY i.created DESC limit 4;
>                                                                QUERY PLAN
>
---------------------------------------------------------------------------------------------------------------------------------------
>   Limit  (cost=18.43..18.44 rows=4 width=32) (actual time=0.418..0.418
> rows=4 loops=1)
>     ->  Sort  (cost=18.43..18.97 rows=216 width=32) (actual
> time=0.418..0.418 rows=4 loops=1)
>           Sort Key: i.created
>           Sort Method:  top-N heapsort  Memory: 25kB
>           ->  Nested Loop Left Join  (cost=1.01..15.19 rows=216 width=32)
> (actual time=0.022..0.290 rows=216 loops=1)
>                 Join Filter: (a.id = i.albumid)
>                 Filter: (((a.id IS NULL) AND (i.visible_for = ANY
> ('{0,1}'::integer[]))) OR (a.visible_for = ANY ('{0,1}'::integer[])))
>                 ->  Seq Scan on items i  (cost=0.00..8.24 rows=216
> width=26) (actual time=0.012..0.153 rows=216 loops=1)
>                       Filter: ((userid = 564667) AND (type = 1))
>                 ->  Materialize  (cost=1.01..1.02 rows=1 width=6) (actual
> time=0.000..0.000 rows=0 loops=216)
>                       ->  Seq Scan on albums a  (cost=0.00..1.01 rows=1
> width=6) (actual time=0.006..0.006 rows=0 loops=1)
>                             Filter: ((id IS NULL) OR (visible_for = ANY
> ('{0,1}'::integer[])))
>   Total runtime: 0.464 ms
> (13 rows)

Hmm, it shouldn't be pushing the OR qual down to the base scan like that
...

Do you have an index on albums.visible_for?  Experimenting here, it
seems that this failure mode occurs only if all the OR-clause elements
are indexable.

            regards, tom lane