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
