Message-ID: <3FC63057.3080009@klaster.net>
Date: Thu, 27 Nov 2003 18:11:51 +0100
From: Tomasz Myrta <jasiek@klaster.net>
User-Agent: Mozilla/5.0 (Windows; U; Win 9x 4.90; pl-PL;
 rv:1.5) Gecko/20031007
MIME-Version: 1.0
To: Lista dyskusyjna pgsql-sql <pgsql-sql@postgresql.org>
Subject: explicit joins wrong planning
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit

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