LEFT JOIN ...
Morning ...
I'm trying to wrack my brain over something here, and no matter
how I try and look at it, I'm drawing a blank ...
I have two tables that are dependent on each other:
notes (86736 tuples) and note_links (173473 tuples)
The relationship is that one note can have several 'ppl' link'd to
it ...
I have a third table: calendar (11014 tuples) ... those calendar
entries link to a note.
So you have something like:
personA ---
personB --|--> note_links --> notes --[maybe]--> calendar entry
personC ---
now, the query I'm workign with is:
SELECT n.note, n.nid, n.type, c.act_type, c.status, nl.contact_lvl,
CASE WHEN c.act_start IS NULL
THEN date_part('epoch', n.added)
ELSE date_part('epoch', c.act_start)
END AS start
FROM note_links nl, notes n LEFT JOIN calendar c ON (n.nid = c.nid)
WHERE (n.type = 'A' OR n.type = 'N' OR n.type = 'H' OR n.type = 'C')
AND (nl.id = 15748 AND contact_lvl = 'company')
AND n.nid = nl.nid
ORDER BY start DESC;
Which explains out as:
NOTICE: QUERY PLAN:
Sort (cost=7446.32..7446.32 rows=1 width=88)
-> Nested Loop (cost=306.52..7446.31 rows=1 width=88)
-> Index Scan using note_links_id on note_links nl (cost=0.00..3.49 rows=1 width=16)
-> Materialize (cost=6692.63..6692.63 rows=60015 width=72)
-> Hash Join (cost=306.52..6692.63 rows=60015 width=72)
-> Seq Scan on notes n (cost=0.00..2903.98 rows=60015 width=36)
-> Hash (cost=206.22..206.22 rows=10122 width=36)
-> Seq Scan on calendar c (cost=0.00..206.22 rows=10122 width=36)
EXPLAIN
and takes forever to run ...
Now, if I eliminate the LEFT JOIN part of the above, *one* tuple is
returned ... so even with the LEFT JOIN, only *one* tuple is going to be
returned ...
Is there some way to write the above so that it evaluates:
WHERE (n.type = 'A' OR n.type = 'N' OR n.type = 'H' OR n.type = 'C')
AND (nl.id = 15748 AND contact_lvl = 'company')
AND n.nid = nl.nid
first, so that it only has to do the LEFT JOIN on the *one* n.nid that is
returned, instead of the 86736 that are in the table?
Marc G. Fournier ICQ#7615664 IRC Nick: Scrappy
Systems Administrator @ hub.org
primary: scrappy@hub.org secondary: scrappy@{freebsd|postgresql}.org
The Hermit Hacker <scrappy@hub.org> writes:
FROM note_links nl, notes n LEFT JOIN calendar c ON (n.nid = c.nid)
WHERE (n.type = 'A' OR n.type = 'N' OR n.type = 'H' OR n.type = 'C')
AND (nl.id = 15748 AND contact_lvl = 'company')
AND n.nid = nl.nid
ORDER BY start DESC;
Is there some way to write the above so that it evaluates:
first, so that it only has to do the LEFT JOIN on the *one* n.nid that is
returned, instead of the 86736 that are in the table?
Try adding ... AND n.nid = 15748 ... to the WHERE. It's not very
bright about making that sort of transitive-equality deduction for
itself...
regards, tom lane
I think that using INNER JOIN between nl and n (on n.nid=nl.nid) or
joining those tables in a subquery might work.
On Mon, 18 Jun 2001, The Hermit Hacker wrote:
Show quoted text
Is there some way to write the above so that it evaluates:
WHERE (n.type = 'A' OR n.type = 'N' OR n.type = 'H' OR n.type = 'C')
AND (nl.id = 15748 AND contact_lvl = 'company')
AND n.nid = nl.nidfirst, so that it only has to do the LEFT JOIN on the *one* n.nid that is
returned, instead of the 86736 that are in the table?
On Mon, 18 Jun 2001, Tom Lane wrote:
The Hermit Hacker <scrappy@hub.org> writes:
FROM note_links nl, notes n LEFT JOIN calendar c ON (n.nid = c.nid)
WHERE (n.type = 'A' OR n.type = 'N' OR n.type = 'H' OR n.type = 'C')
AND (nl.id = 15748 AND contact_lvl = 'company')
AND n.nid = nl.nid
ORDER BY start DESC;Is there some way to write the above so that it evaluates:
first, so that it only has to do the LEFT JOIN on the *one* n.nid that is
returned, instead of the 86736 that are in the table?Try adding ... AND n.nid = 15748 ... to the WHERE. It's not very
bright about making that sort of transitive-equality deduction for
itself...
n.nid is the note id ... nl.id is the contact id ...
I'm trying to pull out all notes for the company with an id of 15748:
sepick=# select * from note_links where id = 15748;
nid | id | contact_lvl | owner
-------+-------+-------------+-------
84691 | 15748 | company | f
(1 row)
The Hermit Hacker <scrappy@hub.org> writes:
Try adding ... AND n.nid = 15748 ... to the WHERE.
n.nid is the note id ... nl.id is the contact id ...
Ooops, I misread "n.nid = nl.nid" as "n.nid = nl.id". Sorry for the
bogus advice.
Try rephrasing as
FROM (note_links nl JOIN notes n ON (n.nid = nl.nid))
LEFT JOIN calendar c ON (n.nid = c.nid)
WHERE ...
The way you were writing it forced the LEFT JOIN to be done first,
whereas what you want is for the note_links-to-notes join to be done
first. See
http://www.ca.postgresql.org/users-lounge/docs/7.1/postgres/explicit-joins.html
regards, tom lane
Perfect, thank you ... i knew I was overlooking something obvious ... the
query just flies now ...
On Mon, 18 Jun 2001, Tom Lane wrote:
The Hermit Hacker <scrappy@hub.org> writes:
Try adding ... AND n.nid = 15748 ... to the WHERE.
n.nid is the note id ... nl.id is the contact id ...
Ooops, I misread "n.nid = nl.nid" as "n.nid = nl.id". Sorry for the
bogus advice.Try rephrasing as
FROM (note_links nl JOIN notes n ON (n.nid = nl.nid))
LEFT JOIN calendar c ON (n.nid = c.nid)
WHERE ...The way you were writing it forced the LEFT JOIN to be done first,
whereas what you want is for the note_links-to-notes join to be done
first. See
http://www.ca.postgresql.org/users-lounge/docs/7.1/postgres/explicit-joins.htmlregards, tom lane
Marc G. Fournier ICQ#7615664 IRC Nick: Scrappy
Systems Administrator @ hub.org
primary: scrappy@hub.org secondary: scrappy@{freebsd|postgresql}.org