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 -----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 6: Have you searched our list archives? http://archives.postgresql.org
