LEFT JOIN ... - Mailing list pgsql-sql

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
ISNULL                                 THEN date_part('epoch', n.added)                                 ELSE
date_part('epoch',c.act_start)                               END AS start                          FROM note_links nl,
notesn 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')
ANDn.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
Scanusing note_links_id on note_links nl  (cost=0.00..3.49 rows=1 width=16)       ->  Materialize
(cost=6692.63..6692.63rows=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.22rows=10122 width=36)                         ->  Seq Scan on calendar c  (cost=0.00..206.22
rows=10122width=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