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

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

From: Dmitry Tkach <dmitry(at)openratings(dot)com>
To: Jie Liang <jie(at)stbernard(dot)com>
Cc: pgsql-general(at)postgresql(dot)org, pgsql-sql(at)postgresql(dot)org,pgsql-bugs(at)postgresql(dot)org
Subject: It is a bug in pred_test()! (Was: [SQL] Please, HELP! Why is the query plan so wrong???)
Date: 2002-07-12 16:17:18
Message-ID: 3D2F010E.4050707@openratings.com (view raw or flat)
Thread:
Lists: pgsql-bugspgsql-generalpgsql-sql
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
>>
>




In response to

Responses

pgsql-sql by date

Next:From: eric soroosDate: 2002-07-12 17:22:11
Subject: Re: Query kill
Previous:From: Josh BerkusDate: 2002-07-12 15:56:33
Subject: Re: config postgresql.conf??

pgsql-bugs by date

Next:From: Niranjan ShivaratriDate: 2002-07-12 17:47:20
Subject: Unable to install postgres on Mips platform.
Previous:From: Jie LiangDate: 2002-07-12 15:29:55
Subject: Re: [SQL] Please, HELP! Why is the query plan so wrong???

pgsql-general by date

Next:From: Chris AlbertsonDate: 2002-07-12 16:40:52
Subject: Re: PostgreSQL in mission-critical system
Previous:From: Neil ConwayDate: 2002-07-12 16:15:45
Subject: Re: PostgreSQL in mission-critical system

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