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:[EMAIL PROTECTED]] >Sent: Friday, July 12, 2002 7:34 AM >To: Jie Liang >Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED] >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:[EMAIL PROTECTED]] >>Sent: Thursday, July 11, 2002 3:51 PM >>To: [EMAIL PROTECTED]; [EMAIL PROTECTED] >>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 [EMAIL PROTECTED] so that your >>message can get through to the mailing list cleanly >> > ---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
