explicit joins wrong planning

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

Responses

Browse pgsql-sql by date

  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