From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Sebastian Böck <sebastianboeck(at)freenet(dot)de> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Bug with index-usage? |
Date: | 2005-11-14 18:12:36 |
Message-ID: | 23613.1131991956@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
=?ISO-8859-1?Q?Sebastian_B=F6ck?= <sebastianboeck(at)freenet(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Michelle Konzack | 2005-11-14 18:36:44 | 3 x PostgreSQL in cluster/redunant |
Previous Message | Sebastian Böck | 2005-11-14 18:06:02 | Re: Bug with index-usage? |