To: pgsql-sql@postgreSQL.org
Subject: left join query does not perform well
Message-ID: <m3hehja80c.fsf@dep4.fciencias.unam.mx>
From: Manuel Sugawara <masm@fciencias.unam.mx>
User-Name: Manuel Sugawara
User-Mail-Address: masm@fciencias.unam.mx
Date: 25 Aug 2002 00:20:09 -0500
Lines: 59
User-Agent: Gnus/5.0808 (Gnus v5.8.8) XEmacs/21.4 (Common Lisp)
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: quoted-printable


Hi all,

I have three tables that are related in the following way:

'----------------------------------------
| epr_ord_grupo < 1 - n > epr_ord_profesor < 0 - n > epr_ord_horario
`----------------------------------------

In average there is 1.0407716514 rows in epr_ord_horario for each row
in epr_ord_profesor, but since there may be 0 I use the query
explained below:

'----------------------------------------
| test=3D# explain analyze SELECT grupo.grupo_id FROM (epr_vord_grupo AS gr=
upo INNER JOIN (epr_vord_profesor AS profesor LEFT JOIN epr_vord_horario AS=
 horario ON (profesor.profesor_id =3D horario.profesor_id)) ON (grupo.grupo=
_id =3D profesor.grupo_id)) WHERE (grupo.a=F1o_semestre =3D cast('20022' as=
 integer)) AND (grupo.grupo =3D cast('4021' as int)) ORDER BY grupo.grupo_i=
d, profesor.cargo, profesor.profesor_id, horario.horario_id;
| NOTICE:  QUERY PLAN:
|=20
| Sort  (cost=3D1165.25..1165.25 rows=3D2 width=3D24) (actual time=3D3113.3=
0..3113.31 rows=3D3 loops=3D1)
|   ->  Hash Join  (cost=3D670.71..1165.24 rows=3D2 width=3D24) (actual tim=
e=3D3016.64..3113.08 rows=3D3 loops=3D1)
|         ->  Merge Join  (cost=3D665.46..1110.66 rows=3D9859 width=3D20) (=
actual time=3D2774.61..3058.77 rows=3D10157 loops=3D1)
|               ->  Index Scan using epr_ord_profesor_pkey on epr_ord_profe=
sor  (cost=3D0.00..306.45 rows=3D9859 width=3D12) (actual time=3D124.07..27=
5.04 rows=3D9859 loops=3D1)
|               ->  Sort  (cost=3D665.46..665.46 rows=3D7607 width=3D52) (a=
ctual time=3D2650.49..2660.55 rows=3D7607 loops=3D1)
|                     ->  Subquery Scan horario  (cost=3D0.00..175.07 rows=
=3D7607 width=3D52) (actual time=3D20.62..2593.15 rows=3D7607 loops=3D1)
|                           ->  Seq Scan on epr_ord_horario h  (cost=3D0.00=
..175.07 rows=3D7607 width=3D52) (actual time=3D20.61..2538.44 rows=3D7607 =
loops=3D1)
|         ->  Hash  (cost=3D5.25..5.25 rows=3D1 width=3D4) (actual time=3D2=
6.26..26.26 rows=3D0 loops=3D1)
|               ->  Index Scan using llave primaria-asemg on epr_ord_grupo =
g  (cost=3D0.00..5.25 rows=3D1 width=3D4) (actual time=3D26.24..26.25 rows=
=3D1 loops=3D1)
| Total runtime: 3117.48 msec
|=20
| EXPLAIN
`----------------------------------------

However the full join (which is correct almost always) of the three
tables looks like:

'----------------------------------------
| test=3D# explain analyze SELECT grupo.grupo_id FROM epr_vord_grupo grupo,=
 epr_vord_profesor AS profesor, epr_vord_horario AS horario WHERE  (profeso=
r.profesor_id =3D horario.profesor_id) AND (grupo.grupo_id =3D profesor.gru=
po_id) AND (grupo.a=F1o_semestre =3D cast('20022' as integer)) AND (grupo.g=
rupo =3D cast('4021' as int)) ORDER BY grupo.grupo_id, profesor.cargo, prof=
esor.profesor_id, horario.horario_id;
| NOTICE:  QUERY PLAN:
|=20
| Sort  (cost=3D14.91..14.91 rows=3D2 width=3D24) (actual time=3D0.53..0.53=
 rows=3D3 loops=3D1)
|   ->  Nested Loop  (cost=3D0.00..14.90 rows=3D2 width=3D24) (actual time=
=3D0.19..0.30 rows=3D3 loops=3D1)
|         ->  Nested Loop  (cost=3D0.00..8.48 rows=3D2 width=3D16) (actual =
time=3D0.14..0.17 rows=3D2 loops=3D1)
|               ->  Index Scan using llave primaria-asemg on epr_ord_grupo =
g  (cost=3D0.00..5.25 rows=3D1 width=3D4) (actual time=3D0.08..0.08 rows=3D=
1 loops=3D1)
|               ->  Index Scan using epr_ord_profe_gpo on epr_ord_profesor =
 (cost=3D0.00..3.20 rows=3D2 width=3D12) (actual time=3D0.04..0.06 rows=3D2=
 loops=3D1)
|         ->  Index Scan using epr_ord_horario_prf on epr_ord_horario h  (c=
ost=3D0.00..3.04 rows=3D1 width=3D8) (actual time=3D0.03..0.05 rows=3D2 loo=
ps=3D2)
| Total runtime: 1.15 msec
|=20
| EXPLAIN
`----------------------------------------

Ouch, 3117.48 msec vs. 1.15 msec is a huge difference. I need
something else? or may be postgres optimizer can't cope with
left/right joins?

I was thinking to add a dummy row in epr_ord_horario to ensure that
the full join is always correct but may be a better solution arises
from the expertise of the list :-)

Regards,
Manuel.