Re: Bug with index-usage?

=?ISO-8859-1?Q?Sebastian_B=F6ck?= <sebastianboeck@freenet.de> writes:
> I get unpredictibale results selecting from a view depending on
> index-usage.

It's not actually *using* the indexes, although presence of the indexes
does seem to be needed to trigger the bug:

regression=# explain SELECT * from test WHERE type = 'a';
                                        QUERY PLAN
-------------------------------------------------------------------------------------------
 Nested Loop Left Join  (cost=0.00..2.29 rows=1 width=40)
   Join Filter: (("outer"."type" = 'c'::text) OR ("outer"."type" = 'b'::text))
   ->  Seq Scan on test2  (cost=0.00..1.16 rows=1 width=40)
         Filter: (("type" = 'a'::text) AND (("type" = 'c'::text) OR ("type" = 'b'::text)))
   ->  Seq Scan on test2 t2  (cost=0.00..1.11 rows=1 width=0)
         Filter: ("type" = 'a'::text)
(6 rows)

regression=# drop index index_b;
DROP INDEX
regression=# explain SELECT * from test WHERE type = 'a';
                                  QUERY PLAN
-------------------------------------------------------------------------------
 Nested Loop Left Join  (cost=0.00..2.24 rows=1 width=40)
   Join Filter: (("outer"."type" = 'c'::text) OR ("outer"."type" = 'b'::text))
   ->  Seq Scan on test2  (cost=0.00..1.11 rows=1 width=40)
         Filter: ("type" = 'a'::text)
   ->  Seq Scan on test2 t2  (cost=0.00..1.11 rows=1 width=0)
         Filter: ("type" = 'a'::text)
(6 rows)

It looks like the problem is that the new 8.1 OR-index-qual code is
confused about when it can apply outer-join conditions.  It shouldn't be
propagating the outer-join condition into the scan condition on test2,
but it is.  Will fix.

            regards, tom lane