LEFT JOINs takes forever...almost. - Mailing list pgsql-admin

Hi, help needed!

I'm having a slight performace problem here, i'm using pg 7.2.1 on a
Linux(RH.7.2).
My query that is causing me problems looks like this:

SELECT v.*,vd.vessel_name,vd.start_date as vd_start_date,vd.end_date as
vd_end_date,d.firstname||' '||d.lastname as customer_name
FROM vessel as v
LEFT JOIN vesseldebtor as vd ON v.callsign=vd.callsign
LEFT JOIN comment as c ON v.comment_id=c.comment_id
LEFT JOIN debtor as d ON vd.customer_id=d.customer_id
WHERE vd.customer_id = '35034694'
ORDER BY vd.start_date ASC

As we see there is four tables in the query, the attentive reader
notices that
I don't select anything from the table comment, but I'm going to do that
later
on so please disregard that for now.

The four tables are:
vessel, vesseldebtor, debtor and comment.

When running a Explain Anaylze on the query this is returned:

Sort  (cost=24333.42..24333.42 rows=41392 width=157) (actual
time=3181.32..3181.33 rows=3 loops=1)
  ->  Hash Join  (cost=3083.88..15830.55 rows=41392 width=157) (actual
time=2943.43..3181.23 rows=3 loops=1)
        ->  Hash Join  (cost=1869.92..11545.77 rows=41392 width=153)
(actual time=2357.00..2672.09 rows=3 loops=1)
              ->  Merge Join  (cost=0.00..6737.57 rows=41392 width=128)
(actual time=1683.54..1787.73 rows=3 loops=1)
                    ->  Index Scan using vessel_pkey on vessel v
(cost=0.00..2498.30 rows=41392 width=95) (actual time=0.22..617.74
rows=41392 loops=1)
                    ->  Index Scan using vesseldebtor_pkey on
vesseldebtor vd  (cost=0.00..3289.35 rows=48368 width=33) (actual
time=0.21..495.45 rows=48368 loops=1)
              ->  Hash  (cost=1504.94..1504.94 rows=41194 width=25)
(actual time=408.73..408.73 rows=0 loops=1)
                    ->  Seq Scan on debtor d  (cost=0.00..1504.94
rows=41194 width=25) (actual time=0.06..184.87 rows=41194 loops=1)
        ->  Hash  (cost=979.16..979.16 rows=39516 width=4) (actual
time=221.50..221.50 rows=0 loops=1)
              ->  Seq Scan on comment c  (cost=0.00..979.16 rows=39516
width=4) (actual time=0.05..82.59 rows=39516 loops=1)
Total runtime: 3181.60 msec

Whereas a similar query using INNER joins like this:

EXPLAIN ANALYZE SELECT v.*,vd.vessel_name,vd.start_date as
vd_start_date,vd.end_date as
vd_end_date,d.firstname||' '||d.lastname as customer_name
FROM vessel as v, vesseldebtor as vd, comment as c, debtor as d
WHERE
v.callsign=vd.callsign AND
v.comment_id=c.comment_id AND
vd.customer_id=d.customer_id AND
vd.customer_id = '35034694'
ORDER BY vd.start_date ASC

And the output:

Sort  (cost=54.29..54.29 rows=4 width=157) (actual time=1.39..1.39
rows=3 loops=1)
  ->  Nested Loop  (cost=0.00..54.25 rows=4 width=157) (actual
time=0.83..1.31 rows=3 loops=1)
        ->  Nested Loop  (cost=0.00..40.40 rows=4 width=132) (actual
time=0.63..1.04 rows=3 loops=1)
              ->  Nested Loop  (cost=0.00..29.27 rows=4 width=128)
(actual time=0.44..0.70 rows=3 loops=1)
                    ->  Index Scan using vd_customer_id_idx on
vesseldebtor vd  (cost=0.00..8.45 rows=4 width=33) (actual
time=0.22..0.31 rows=3 loops=1)
                    ->  Index Scan using vessel_pkey on vessel v
(cost=0.00..5.71 rows=1 width=95) (actual time=0.11..0.12 rows=1
loops=3)
              ->  Index Scan using comment_pkey on comment c
(cost=0.00..3.05 rows=1 width=4) (actual time=0.10..0.10 rows=1 loops=3)
        ->  Index Scan using debtor_pkey on debtor d  (cost=0.00..3.79
rows=1 width=25) (actual time=0.07..0.07 rows=1 loops=3)
Total runtime: 1.64 msec

EXPLAIN

Can someone please explain why the first query is 3000 times slower?
Surely there must be a any way to speed it up?
If the solution is not that trivial, someone please tell me so that I
can describe the columns and indexes more in detail.


Regards,

Niclas Gustafsson
CodeSense AB