Received: from exchange.hq.openratings.com (adsl-141-154-210-2.ba-dsg.net [141.154.210.2]) by postgresql.org (Postfix) with ESMTP id 08697475908; Fri, 12 Jul 2002 10:38:32 -0400 (EDT) Received: from openratings.com (cancun.hq.openratings.com [10.0.0.225]) by exchange.hq.openratings.com with SMTP (Microsoft Exchange Internet Mail Service Version 5.5.2653.13) id 34WXN60Y; Fri, 12 Jul 2002 10:38:36 -0400 Message-ID: <3D2EE8F3.5060904@openratings.com> Date: Fri, 12 Jul 2002 10:34:27 -0400 From: Dmitry Tkach User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:0.9.7+) Gecko/20020123 X-Accept-Language: en-us MIME-Version: 1.0 To: Jie Liang Cc: pgsql-general@postgresql.org, pgsql-sql@postgresql.org Subject: Re: [SQL] Please, HELP! Why is the query plan so wrong??? References: <7C760DAA511DC74B99E7D22189F786F101BF211C@MAIL01.stbernard.com> Content-Type: text/plain; charset=KOI8-R; format=flowed Content-Transfer-Encoding: 7bit X-Archive-Number: 200207/765 X-Sequence-Number: 27252 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@openratings.com] >Sent: Thursday, July 11, 2002 3:51 PM >To: pgsql-general@postgresql.org; pgsql-sql@postgresql.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@postgresql.org so that your >message can get through to the mailing list cleanly >