From: | Jie Liang <jie(at)stbernard(dot)com> |
---|---|
To: | 'Dmitry Tkach' <dmitry(at)openratings(dot)com>, pgsql-general(at)postgresql(dot)org, pgsql-sql(at)postgresql(dot)org |
Subject: | Re: [SQL] Please, HELP! Why is the query plan so wrong??? |
Date: | 2002-07-11 23:26:43 |
Message-ID: | 7C760DAA511DC74B99E7D22189F786F101BF211C@MAIL01.stbernard.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general pgsql-sql |
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: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
From | Date | Subject | |
---|---|---|---|
Next Message | Jan Wieck | 2002-07-11 23:32:51 | Re: SPI_exec |
Previous Message | John Hall | 2002-07-11 23:16:05 | Re: Jan's Name (Was: Re: I am being interviewed by OReilly) |
From | Date | Subject | |
---|---|---|---|
Next Message | Rudi Starcevic | 2002-07-11 23:27:04 | PostgreSQL - contrib/xml |
Previous Message | Dmitry Tkach | 2002-07-11 22:50:44 | Please, HELP! Why is the query plan so wrong??? |