Received: from news.hub.org (news.hub.org [64.49.215.80])
	by postgresql.org (Postfix) with ESMTP
	id 95D06475A58; Thu, 11 Jul 2002 18:55:22 -0400 (EDT)
Received: by news.hub.org (Postfix, from userid 8)
	id B96B2381E8E; Thu, 11 Jul 2002 18:54:51 -0400 (EDT)
From: Dmitry Tkach <dmitry@openratings.com>
X-Newsgroups: comp.databases.postgresql.sql,comp.databases.postgresql.general
Subject: Please, HELP! Why is the query plan so wrong???
Date: Thu, 11 Jul 2002 18:50:44 -0400
Organization: Hub.Org Networking Services (http://www.hub.org)
Lines: 42
Message-ID: <3D2E0BC4.1000506@openratings.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit
X-Complaints-To: news@hub.org
User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:0.9.7+) Gecko/20020123
X-Accept-Language: en-us
To: pgsql-general@postgresql.org, pgsql-sql@postgresql.org
X-Archive-Number: 200207/698
X-Sequence-Number: 27185

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