From: Dmitry Tkach <dmitry@openratings.com>
To: pgsql-general@postgresql.org
To: pgsql-sql@postgresql.org
Subject: Please, HELP! Why is the query plan so wrong???
Date: Thu, 11 Jul 2002 18:50:44 -0400
Message-ID: <3D2E0BC4.1000506@openratings.com> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: dmitry@openratings.com, pgsql-general@postgresql.org
Subject: Re: Please, HELP! Why is the query plan so wrong???
In-Reply-To: <3D2E0BC4.1000506@openratings.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox