explicit joins wrong planning
От | Tomasz Myrta |
---|---|
Тема | explicit joins wrong planning |
Дата | |
Msg-id | 3FC63057.3080009@klaster.net обсуждение исходный текст |
Ответы |
Re: explicit joins wrong planning
|
Список | 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
В списке pgsql-sql по дате отправления: