pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedPlease, HELP! Why is the query plan so wrong???
4+ messages / 2 participants
[nested] [flat]
* Please, HELP! Why is the query plan so wrong???
@ 2002-07-11 22:50 Dmitry Tkach <dmitry@openratings.com>
0 siblings, 0 replies; 4+ messages in thread
From: Dmitry Tkach @ 2002-07-11 22:50 UTC (permalink / raw)
To: pgsql-general@postgresql.org; pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: [SQL] Please, HELP! Why is the query plan so wrong???
@ 2002-07-11 23:26 Jie Liang <jie@stbernard.com>
0 siblings, 1 reply; 4+ messages in thread
From: Jie Liang @ 2002-07-11 23:26 UTC (permalink / raw)
To: Dmitry Tkach <dmitry@openratings.com>; pgsql-general@postgresql.org; 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@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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: [SQL] Please, HELP! Why is the query plan so wrong???
@ 2002-07-12 14:34 Dmitry Tkach <dmitry@openratings.com>
parent: Jie Liang <jie@stbernard.com>
0 siblings, 0 replies; 4+ messages in thread
From: Dmitry Tkach @ 2002-07-12 14:34 UTC (permalink / raw)
To: Jie Liang <jie@stbernard.com>; +Cc: pgsql-general@postgresql.org; pgsql-sql
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
>
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: [SQL] Please, HELP! Why is the query plan so wrong???
@ 2002-07-12 15:29 Jie Liang <jie@stbernard.com>
0 siblings, 0 replies; 4+ messages in thread
From: Jie Liang @ 2002-07-12 15:29 UTC (permalink / raw)
To: Dmitry Tkach <dmitry@openratings.com>; Jie Liang <jie@stbernard.com>; +Cc: pgsql-general@postgresql.org; pgsql-sql
please copy and paste the whole msg and your query!
Note:what I mean ' join key' is the fields that link two tables.
I don't think fb.b=0 is a join key!
Jie Liang
-----Original Message-----
From: Dmitry Tkach [mailto:dmitry@openratings.com]
Sent: Friday, July 12, 2002 7:34 AM
To: Jie Liang
Cc: pgsql-general@postgresql.org; pgsql-sql@postgresql.org
Subject: Re: [SQL] Please, HELP! Why is the query plan so wrong???
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
>
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2002-07-12 15:29 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-07-11 22:50 Please, HELP! Why is the query plan so wrong??? Dmitry Tkach <dmitry@openratings.com>
2002-07-11 23:26 Re: [SQL] Please, HELP! Why is the query plan so wrong??? Jie Liang <jie@stbernard.com>
2002-07-12 14:34 ` Dmitry Tkach <dmitry@openratings.com>
2002-07-12 15:29 Re: [SQL] Please, HELP! Why is the query plan so wrong??? Jie Liang <jie@stbernard.com>
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