From: | Tomasz Myrta <jasiek(at)klaster(dot)net> |
---|---|
To: | Lista dyskusyjna pgsql-sql <pgsql-sql(at)postgresql(dot)org> |
Subject: | explicit joins wrong planning |
Date: | 2003-11-27 17:11:51 |
Message-ID: | 3FC63057.3080009@klaster.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
Hi
SELECT version();
PostgreSQL 7.3.4 on i386-pc-linux-gnu, compiled by GCC i386-linux-gcc
(GCC) 3.3.2 20031005 (Debian prerelease)
Let's say I have 3 tables:
groups (
groupid integer primary key,
name varchar,
begindate date
);
offsets (
offset_id integer,
groupid integer references groups,
offset_value integer
);
events (
offset_id integer references offsets,
event_date date,
primary key (offset_id,event_date)
);
explain analyze select *
from
groups g
join offsets o using (groupid)
join events e on (e.offsetid=o.offset_id and
e.event_date=g.begindate+o.offset_value)
where g.name='some_name';
Postgres doesn't use join on these both fields and doesn't use index
scan properly.
I get:
Hash Cond: ("outer".offset_id = "inner".offset_id)
Join Filter: ("outer".event_date = ("inner".begindate +
"inner".offset_value))
Why?
I lost few hours trying to fix it and I found, that copying one of these
conditions into where clause solved my problem:
explain analyze select *
from
groups g
join offsets o using (groupid)
join events e on (e.offsetid=o.offset_id and
e.event_date=g.begindate+o.offset_value)
where g.name='some_name' and e.offsetid=o.offset_id;
Join Filter: ("outer".event_date = ("inner".begindate +
"inner".offset_value))
Nested Loop...
Join Filter: ("outer".offset_id = "inner".offset_id)
Why? What was I doing wrong?
Regards,
Tomasz Myrta
From | Date | Subject | |
---|---|---|---|
Next Message | Richard Huxton | 2003-11-27 17:31:38 | Re: Scaler forms as function arguments |
Previous Message | Andreas Tille | 2003-11-27 16:40:43 | Re: Scaler forms as function arguments |