From: Tomasz Myrta <jasiek@klaster.net>
To: Lista dyskusyjna pgsql-sql <pgsql-sql@postgresql.org>
Subject: explicit joins wrong planning
Date: Thu, 27 Nov 2003 18:11:51 +0100
Message-ID: <3FC63057.3080009@klaster.net> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: jasiek@klaster.net
Subject: Re: explicit joins wrong planning
In-Reply-To: <3FC63057.3080009@klaster.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox