[SQL] explicit joins wrong planning

[Tomasz Myrta]

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




---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
              http://archives.postgresql.org