Why is outer Join way quicker? - Mailing list pgsql-general

Hi.

We have found that by using an Outer Join it speeds up this query
considerably.

Why is that?

According to the explain plan the hold up has to do with a single nexted
loop and a "Materilize"?  This makes no sense to me.  Can some one
explain.

Thanks. -David

The difference between these two queries is the first uses an outer join
between u and t, while the second (the slower) uses a straight forward
join).

-- Query 1:
SELECT u.upc, t.title, tot.ytd, tot.rtd
FROM   upc u
       LEFT OUTER JOIN title t
          ON t.tcode = u.tcode
       LEFT OUTER JOIN total tot
          ON tot.tcode = t.tcode AND tot.week = 200210 AND
tot.region='TOTAL'
WHERE  u.upc LIKE '%0085392227%'
ORDER BY title ASC
LIMIT 500 OFFSET 0;

-- Query 1: Actual Time: 1 second
0.03user 0.00system 0:00.96elapsed 3%CPU (0avgtext+0avgdata
0maxresident)k
0inputs+0outputs (576major+168minor)pagefaults 0swaps

-- Query 1:  QUERY PLAN:
Limit (cost=2801.10..2801.10 rows=1 width=108)
 -> Sort (cost=2801.10..2801.10 rows=1 width=108)
    -> Nested Loop (cost=0.00..2801.09 rows=1 width=108)
       -> Nested Loop (cost=0.00..2796.06 rows=1 width=48)
          -> Seq Scan on upc u (cost=0.00..2791.43 rows=1 width=24)
          -> Index Scan using title_pkey on title t (cost=0.00..4.63
rows=1 width=24)
       -> Index Scan using total_week_tcode_ind on total tot
(cost=0.00..5.01 rows=1 width=60)

-- =======================================
-- Query 2
explain
SELECT u.upc, t.title, tot.ytd, tot.rtd
FROM   upc u,
       title t left outer join total tot
         ON tot.tcode = t.tcode AND tot.week = 200210 AND
tot.region='TOTAL'
WHERE t.tcode = u.tcode and u.upc LIKE '%0085392227%'
ORDER BY title ASC LIMIT 500 OFFSET 0;

-- Query 2: Actual Time:  36 SECONDS!!!
0.01user 0.02system 0:35.33elapsed 0%CPU (0avgtext+0avgdata
0maxresident)k
0inputs+0outputs (576major+168minor)pagefaults 0swaps

Limit (cost=541352.96..541352.96 rows=1 width=108)
 -> Sort (cost=541352.96..541352.96 rows=1 width=108)
    -> Nested Loop (cost=0.00..541352.95 rows=1 width=108)
       -> Seq Scan on upc u (cost=0.00..2791.43 rows=1 width=24)
       -> Materialize (cost=537241.84..537241.84 rows=105575 width=84)
          -> Nested Loop (cost=0.00..537241.84 rows=105575 width=84)
             -> Seq Scan on title t (cost=0.00..6228.75 rows=105575
width=24)
             -> Index Scan using total_week_tcode_ind on total tot
(cost=0.00..5.01 rows=1 width=60)


-- Output (from both Query 1 and Query 2):
     upc      |             title              |  ytd   |   rtd
--------------+--------------------------------+--------+---------
 008539222773 | CATS AND DOGS                  |  65240 | 1080103
 008539222772 | CATS AND DOGS-2001-PS          |  47683 |  480374
 008539222793 | CATS & DOGS                    |        |
 008539222753 | MATRIX/MATRIX REVISITED 2-PACK |    299 |    1395
 008539222783 | SWORDFISH                      |  27992 |  234049
 008539222782 | SWORDFISH                      | 136727 |  987219
(6 rows)