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

toruvinn <toruvinn(at)lain(dot)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