Skip site navigation (1) Skip section navigation (2)

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

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: toruvinn <toruvinn(at)lain(dot)pl>
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: LEFT OUTER JOIN and WHERE madness (8.3.3 bug?)
Date: 2008-06-27 16:56:32
Message-ID: 4733.1214585792@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-bugs
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

In response to

pgsql-bugs by date

Next:From: Craig RingerDate: 2008-06-27 17:26:35
Subject: Re: BUG #4267: initdb fails
Previous:From: toruvinnDate: 2008-06-27 15:27:31
Subject: LEFT OUTER JOIN and WHERE madness (8.3.3 bug?)

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group