It is a bug in pred_test()! (Was: [SQL] Please, HELP! Why is the query plan so wrong???)

Jie Liang wrote:

>please copy and paste the whole msg and your query!
>Note:what I mean ' join key' is the fields that link two tables.
>
The message (query plan) is exactly the same (you can see it in the
bottom of this message).

>
>I don't think fb.b=0 is a join key!
>
Of course not. But it IS using the index on fb. It is the fbr, that is
the problem (and the join key is explicitly specified in the join... on
(..) clause).

Actually, it looks like the problem is caused by the predicate on the index:
if instead of

create index fbr_idx on fbr(a,c) where d is null;

I do just:

create index fbr_idx on fbr(a,c,d);

Then this index is used by the query...

It now looks like a bug in the query planner to me - it seems that it
just doesn't consider indices with predicates for join plans...
I was looking at the source code, and it looks like pred_test() is
responsible for that.
Ater debugging it a little, I found out that, if I list the tables in
the query in the opposite order:

explain select * from fbr, fb where fb.b=0 and fb.a=fbr.a and
fb.c=fbr.c and fbr.d is null;

Then it works (both indexes are used!).

Here is what is causing it:
create_index_paths() calls pred_test () to check whether it is OK to
use a partial index or not.
pred_test () evaluates the index's predicate list against the
restriction clauses of the query.
In my case, the predicate list "d is NULL", so the goal is to find the
equivalent clause in the query.
pred_test () does that by iterating through the query's clauses and
comparing them to the predicate with the equal() function.
equal () calls _equalNullTest(), which in turn calls _equalVar(), that
looks at the varno parameter in the NullTest's argument. Now the value
of varno in the predicate is (of course) 1, (I believe, it always is,
because we don't have multitable indexes), however, the varno in the
clause is 2 (it is the index of the table in the join list), if the fbr
is listed second - and 1, if it is first - so, in the former case it
does not work, and in the latter it does.

Knowing all this doesn't help much unfortunately, because, if you needed
to join 2 (or more) tables that have indexes with predicates, then
whatever order you put them in, would not help (the pred_test() will
only succeed for the first table in the join list) :-(

Perhaps, somebody, familiar with this code could come up with a patch
for this problem?

This would be really great!

Dima

>
>
>Jie Liang
>
>-----Original Message-----
>From: Dmitry Tkach [mailto:dmitry(at)openratings(dot)com]
>Sent: Friday, July 12, 2002 7:34 AM
>To: Jie Liang
>Cc: pgsql-general(at)postgresql(dot)org; pgsql-sql(at)postgresql(dot)org
>Subject: Re: [SQL] Please, HELP! Why is the query plan so wrong???
>
>
>Jie Liang wrote:
>
>>I believe that SQL will use the index of join 'key' when you join the
>>
>tables
>
>>if
>>have any, in your query the (a,c) is the join key but d is not.
>>
>>
>>Jie Liang
>>
>
>Not really... I tried this:
>
>explain select * from fb joing fbr on (fb.a=fbr.a and
>fb.c=fbr.c and fbr.d is null) where fb.b=0
>
>It results in the same query plan (seq scan on fbr).
>
>Dima
>
>
>
>
>>
>>
>>-----Original Message-----
>>From: Dmitry Tkach [mailto:dmitry(at)openratings(dot)com]
>>Sent: Thursday, July 11, 2002 3:51 PM
>>To: pgsql-general(at)postgresql(dot)org; pgsql-sql(at)postgresql(dot)org
>>Subject: [SQL] Please, HELP! Why is the query plan so wrong???
>>
>>
>>Hi, everybody!
>>
>>Here is the problem:
>>
>>test=# create table fb (a int, b int, c datetime);
>>CREATE
>>test=# create table fbr (a int, c datetime, d int);
>>CREATE
>>test=# create unique index fb_idx on fb(b);
>>CREATE
>>test=# create index fbr_idx on fbr(a,c) where d is null;
>>CREATE
>>test=# set enable_seqscan=off;
>>
>>SET VARIABLE
>>rapidb=# explain select * from fb, fbr where fb.b=0 and fb.a=fbr.a and
>>fb.c=fbr.c and fbr.d is null;
>>NOTICE: QUERY PLAN:
>>
>>Hash Join (cost=100000005.82..100001015.87 rows=1 width=32)
>> -> Seq Scan on fbr (cost=100000000.00..100001010.00 rows=5 width=16)
>> -> Hash (cost=5.81..5.81 rows=1 width=16)
>> -> Index Scan using fb_idx on fb (cost=0.00..5.81 rows=1
>>width=16)
>>
>>Could someone PLEASE explain to me, why doesn't it want to use the index on
>>fbr?
>>
>>If I get rid of the join, then it works:
>>
>>test=# explain select * from fbr where a=1 and c=now() and d is null;
>>NOTICE: QUERY PLAN:
>>
>>Index Scan using fbr_idx on fbr (cost=0.00..5.82 rows=1 width=16)
>>
>>What's the catch???
>>
>>Any help would be greatly appreciated!
>>
>>Thanks!
>>
>>Dima
>>
>>
>>
>>
>>---------------------------(end of broadcast)---------------------------
>>TIP 3: if posting/reading through Usenet, please send an appropriate
>>subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
>>message can get through to the mailing list cleanly
>>
>