Sorting when LEFT JOINING to 2 same tables, even aliased. - Mailing list pgsql-performance

Hello to everybody.

I ask your help for a severe problem when doing a query that LEFT JOINs
one table to another ON a field, and then LEFT JOINs again to another
"instance" of a table ON another field which stores the same entity, but
with different meaning.

I include 3 EXPLAIN ANALYZEs:
* The first one, the target (and problematic) query, which runs in 5 to 6
minutes.
* The second one, a variation with the second LEFT JOIN commented out,
which runs in 175 to 450 ms.
* The third one, a variation of the first one with ORDER BY removed, which
gives me about 19 seconds.

Therefore, I feel like there are two problems here the one that raises the
clock to 6 minutes and one that raises it to 20 seconds. I expected a much
lower time. I checked indexes and data types already, they are all fine.
All relevant fields have BTREEs, all PKs have UNIQUE BTREE, and all id and
ext_* fields have 'integer' as data type. Each ext_* has its corresponding
REFERENCES contraint.

I translated all the table and field names to make it easier to read. I
made my best not to let any typo go through.

I'd appreciate any help.

Octavio.

=== First EXPLAIN ANALYZE ===

EXPLAIN ANALYZE
SELECT
    t_materias_en_tira.id AS Id,
    t_clientes.paterno || ' ' || t_clientes.materno || ' ' ||
t_clientes.nombre AS Alumno,
    t_materias.nombre AS Materia,
    t_materias__equivalentes.nombre AS MateriaEquivalente,
    t_grupos.nombre AS Grupo,
    calificacion_final AS Calificacion,
    tipo AS Tipo,
    eer AS EER,
    total_asistencias AS TotalAsistencias,
    total_clases As TotalClases
FROM
    t_materias_en_tira
    LEFT JOIN t_alumnos_en_semestre ON ext_alumno_en_semestre =
t_alumnos_en_semestre.id
    LEFT JOIN t_alumnos ON ext_alumno = t_alumnos.id
    LEFT JOIN t_clientes ON ext_cliente = t_clientes.id
    LEFT JOIN t_materias ON ext_materia = t_materias.id
    LEFT JOIN t_materias AS t_materias__equivalentes ON
ext_materia__equivalencia = t_materias.id
    LEFT JOIN t_grupos ON ext_grupo = t_grupos.id
WHERE
    t_alumnos_en_semestre.ext_ciclo = 2222
ORDER BY
    Alumno, Materia;

This one gave:
                                                                                                     QUERY
PLAN

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=11549.08..11552.11 rows=1210 width=112) (actual
time=311246.000..355615.000 rows=1309321 loops=1)
   Sort Key: (((((t_clientes.paterno)::text || ' '::text) ||
(t_clientes.materno)::text) || ' '::text) ||
(t_clientes.nombre)::text), t_materias.nombre
   InitPlan
     ->  Result  (cost=0.00..0.01 rows=1 width=0) (actual
time=2.000..2.000 rows=1 loops=1)
   ->  Hash Left Join  (cost=1089.25..11487.11 rows=1210 width=112)
(actual time=83.000..19303.000 rows=1309321 loops=1)
         Hash Cond: ("outer".ext_grupo = "inner".id)
         ->  Nested Loop Left Join  (cost=1086.92..11454.53 rows=1210
width=107) (actual time=82.000..9077.000 rows=1309321 loops=1)
               Join Filter: ("outer".ext_materia__equivalencia =
"outer".id) ->  Hash Left Join  (cost=1078.15..1181.93
rows=1210
width=93) (actual time=82.000..275.000 rows=3473 loops=1)
                     Hash Cond: ("outer".ext_materia = "inner".id) ->
Merge Right Join  (cost=1068.43..1154.07
rows=1210 width=71) (actual time=81.000..213.000
rows=3473 loops=1)
                           Merge Cond: ("outer".id = "inner".ext_cliente)
->  Index Scan using t_clientes_pkey on
t_clientes  (cost=0.00..62.87 rows=1847
width=38) (actual time=10.000..34.000 rows=1847
loops=1)
                           ->  Sort  (cost=1068.43..1071.46 rows=1210
width=41) (actual time=71.000..76.000 rows=3473
loops=1)
                                 Sort Key: t_alumnos.ext_cliente
                                 ->  Hash Left Join  (cost=41.12..1006.48
rows=1210 width=41) (actual
time=9.000..61.000 rows=3473 loops=1)
                                       Hash Cond: ("outer".ext_alumno =
"inner".id)
                                       ->  Nested Loop  (cost=0.00..944.18
rows=1210 width=41) (actual
time=3.000..36.000 rows=3473
loops=1)
                                             ->  Index Scan using
i_t_alumnos_en_semestre__ext_ciclo
on t_alumnos_en_semestre
(cost=0.00..8.63 rows=269
width=8) (actual
time=2.000..3.000 rows=457
loops=1)
                                                   Index Cond: (ext_ciclo
= $0)
                                             ->  Index Scan using
i_t_materias_en_tira__ext_alumno_en_semestre
on t_materias_en_tira
(cost=0.00..3.32 rows=12
width=41) (actual
time=0.009..0.035 rows=8
loops=457)
                                                   Index Cond:
(t_materias_en_tira.ext_alumno_en_semestre
= "outer".id)
                                       ->  Hash  (cost=36.50..36.50
rows=1850 width=8) (actual
time=6.000..6.000 rows=0loops=1)
                                             ->  Seq Scan on t_alumnos
(cost=0.00..36.50 rows=1850
width=8) (actual
time=1.000..3.000 rows=1850
loops=1)
                     ->  Hash  (cost=8.77..8.77 rows=377 width=26) (actual
time=1.000..1.000 rows=0 loops=1)
                           ->  Seq Scan on t_materias  (cost=0.00..8.77
rows=377 width=26) (actual time=0.000..1.000
rows=377 loops=1)
               ->  Materialize  (cost=8.77..12.54 rows=377 width=22)
(actual time=0.000..0.175 rows=377 loops=3473)
                     ->  Seq Scan on t_materias t_materias__equivalentes
(cost=0.00..8.77 rows=377 width=22) (actual
time=0.000..1.000 rows=377 loops=1)
         ->  Hash  (cost=2.07..2.07 rows=107 width=13) (actual
time=1.000..1.000 rows=0 loops=1)
               ->  Seq Scan on t_grupos  (cost=0.00..2.07 rows=107
width=13) (actual time=0.000..1.000 rows=107 loops=1)

 Total runtime: 356144.000 ms

=== Second EXPLAIN ANALYZE ===

EXPLAIN ANALYZE
SELECT
    t_materias_en_tira.id AS Id,
    t_clientes.paterno || ' ' || t_clientes.materno || ' ' ||
t_clientes.nombre AS Alumno,
    t_materias.nombre AS Materia,
--    t_materias__equivalentes.nombre AS MateriaEquivalente,
    t_grupos.nombre AS Grupo,
    calificacion_final AS Calificacion,
    tipo AS Tipo,
    eer AS EER,
    total_asistencias AS TotalAsistencias,
    total_clases As TotalClases
FROM
    t_materias_en_tira
    LEFT JOIN t_alumnos_en_semestre ON ext_alumno_en_semestre =
t_alumnos_en_semestre.id
    LEFT JOIN t_alumnos ON ext_alumno = t_alumnos.id
    LEFT JOIN t_clientes ON ext_cliente = t_clientes.id
    LEFT JOIN t_materias ON ext_materia = t_materias.id
--    LEFT JOIN t_materias AS t_materias__equivalentes ON
ext_materia__equivalencia = t_materias.id
    LEFT JOIN t_grupos ON ext_grupo = t_grupos.id
WHERE
    t_alumnos_en_semestre.ext_ciclo = 2222
ORDER BY
    Alumno, Materia;

EXPLAIN ANALYZE
SELECT
    t_materias_en_tira.id AS Id,
    t_clientes.paterno || ' ' || t_clientes.materno || ' ' ||
t_clientes.nombre AS Alumno,
    t_materias.nombre AS Materia,
    t_materias__equivalentes.nombre AS MateriaEquivalente,
    t_grupos.nombre AS Grupo,
    calificacion_final AS Calificacion,
    tipo AS Tipo,
    eer AS EER,
    total_asistencias AS TotalAsistencias,
    total_clases As TotalClases
FROM
    t_materias_en_tira
    LEFT JOIN t_alumnos_en_semestre ON ext_alumno_en_semestre =
t_alumnos_en_semestre.id
    LEFT JOIN t_alumnos ON ext_alumno = t_alumnos.id
    LEFT JOIN t_clientes ON ext_cliente = t_clientes.id
    LEFT JOIN t_materias ON ext_materia = t_materias.id
    LEFT JOIN t_materias AS t_materias__equivalentes ON
ext_materia__equivalencia = t_materias.id
    LEFT JOIN t_grupos ON ext_grupo = t_grupos.id
WHERE
    t_alumnos_en_semestre.ext_ciclo = 2222;

It gave:

                                                                                                  QUERY
PLAN

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=1276.49..1279.51 rows=1210 width=90) (actual
time=341.000..341.000 rows=3473 loops=1)
   Sort Key: (((((t_clientes.paterno)::text || ' '::text) ||
(t_clientes.materno)::text) || ' '::text) ||
(t_clientes.nombre)::text), t_materias.nombre
   InitPlan
     ->  Result  (cost=0.00..0.01 rows=1 width=0) (actual
time=146.000..146.000 rows=1 loops=1)
   ->  Hash Left Join  (cost=1080.48..1214.52 rows=1210 width=90) (actual
time=209.000..284.000 rows=3473 loops=1)
         Hash Cond: ("outer".ext_grupo = "inner".id)
         ->  Hash Left Join  (cost=1078.15..1181.93 rows=1210 width=85)
(actual time=208.000..250.000 rows=3473 loops=1)
               Hash Cond: ("outer".ext_materia = "inner".id)
               ->  Merge Right Join  (cost=1068.43..1154.07 rows=1210
width=67) (actual time=207.000..227.000 rows=3473loops=1)
                     Merge Cond: ("outer".id = "inner".ext_cliente) ->
Index Scan using t_clientes_pkey on t_clientes
(cost=0.00..62.87 rows=1847 width=38) (actual
time=0.000..5.000 rows=1847 loops=1)
                     ->  Sort  (cost=1068.43..1071.46 rows=1210 width=37)
(actual time=207.000..209.000 rows=3473 loops=1)
                           Sort Key: t_alumnos.ext_cliente
                           ->  Hash Left Join  (cost=41.12..1006.48
rows=1210 width=37) (actual
time=152.000..196.000 rows=3473 loops=1)
                                 Hash Cond: ("outer".ext_alumno =
"inner".id) ->  Nested Loop
(cost=0.00..944.18
rows=1210 width=37) (actual
time=146.000..177.000 rows=3473 loops=1)
                                       ->  Index Scan using
i_t_alumnos_en_semestre__ext_ciclo
on t_alumnos_en_semestre
(cost=0.00..8.63 rows=269 width=8)
(actual time=146.000..148.000
rows=457 loops=1)
                                             Index Cond: (ext_ciclo = $0)
                                       ->  Index Scan using
i_t_materias_en_tira__ext_alumno_en_semestre
on t_materias_en_tira
(cost=0.00..3.32 rows=12 width=37)
(actual time=0.009..0.022 rows=8
loops=457)
                                             Index Cond:
(t_materias_en_tira.ext_alumno_en_semestre
= "outer".id)
                                 ->  Hash  (cost=36.50..36.50 rows=1850
width=8) (actual time=6.000..6.000 rows=0
loops=1)
                                       ->  Seq Scan on t_alumnos
(cost=0.00..36.50 rows=1850
width=8) (actual time=0.000..3.000
rows=1850 loops=1)
               ->  Hash  (cost=8.77..8.77 rows=377 width=26) (actual
time=1.000..1.000 rows=0 loops=1)
                     ->  Seq Scan on t_materias  (cost=0.00..8.77 rows=377
width=26) (actual time=0.000..0.000 rows=377loops=1)
         ->  Hash  (cost=2.07..2.07 rows=107 width=13) (actual
time=1.000..1.000 rows=0 loops=1)
               ->  Seq Scan on t_grupos  (cost=0.00..2.07 rows=107
width=13) (actual time=0.000..0.000 rows=107 loops=1)

 Total runtime: 346.000 ms

=== Third EXPLAIN ANALYZE ===

EXPLAIN ANALYZE
SELECT
    t_materias_en_tira.id AS Id,
    t_clientes.paterno || ' ' || t_clientes.materno || ' ' ||
t_clientes.nombre AS Alumno,
    t_materias.nombre AS Materia,
    t_materias__equivalentes.nombre AS MateriaEquivalente,
    t_grupos.nombre AS Grupo,
    calificacion_final AS Calificacion,
    tipo AS Tipo,
    eer AS EER,
    total_asistencias AS TotalAsistencias,
    total_clases As TotalClases
FROM
    t_materias_en_tira
    LEFT JOIN t_alumnos_en_semestre ON ext_alumno_en_semestre =
t_alumnos_en_semestre.id
    LEFT JOIN t_alumnos ON ext_alumno = t_alumnos.id
    LEFT JOIN t_clientes ON ext_cliente = t_clientes.id
    LEFT JOIN t_materias ON ext_materia = t_materias.id
    LEFT JOIN t_materias AS t_materias__equivalentes ON
ext_materia__equivalencia = t_materias.id
    LEFT JOIN t_grupos ON ext_grupo = t_grupos.id
WHERE
    t_alumnos_en_semestre.ext_ciclo = 2222;

Result:

                                                                                                     QUERY
PLAN

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Hash Left Join  (cost=484.34..4470.54 rows=459 width=112) (actual
time=70.000..18241.000 rows=1309321 loops=1)
   Hash Cond: ("outer".ext_grupo = "inner".id)
   ->  Nested Loop Left Join  (cost=482.01..4456.73 rows=459 width=107)
(actual time=70.000..7912.000 rows=1309321 loops=1)
         Join Filter: ("outer".ext_materia__equivalencia = "outer".id) ->
Hash Left Join  (cost=473.24..554.49 rows=459 width=93)
(actual time=70.000..142.000 rows=3473 loops=1)
               Hash Cond: ("outer".ext_materia = "inner".id)
               ->  Merge Right Join  (cost=463.52..537.90 rows=459
width=71) (actual time=67.000..109.000 rows=3473 loops=1)
                     Merge Cond: ("outer".id = "inner".ext_cliente) ->
Index Scan using t_clientes_pkey on t_clientes
(cost=0.00..62.87 rows=1847 width=38) (actual
time=0.000..14.000 rows=1847 loops=1)
                     ->  Sort  (cost=463.52..464.67 rows=459 width=41)
(actual time=67.000..69.000 rows=3473 loops=1)
                           Sort Key: t_alumnos.ext_cliente
                           ->  Merge Right Join  (cost=379.40..443.23
rows=459 width=41) (actual time=34.000..57.000
rows=3473 loops=1)
                                 Merge Cond: ("outer".id =
"inner".ext_alumno)
                                 ->  Index Scan using t_alumnos_pkey on
t_alumnos  (cost=0.00..52.35 rows=1850
width=8) (actual time=0.000..4.000
rows=1850 loops=1)
                                 ->  Sort  (cost=379.40..380.55 rows=459
width=41) (actual time=34.000..36.000
rows=3473 loops=1)
                                       Sort Key:
t_alumnos_en_semestre.ext_alumno
                                       ->  Nested Loop  (cost=0.00..359.11
rows=459 width=41) (actual
time=0.000..21.000 rows=3473
loops=1)
                                             ->  Index Scan using
i_t_alumnos_en_semestre__ext_ciclo
on t_alumnos_en_semestre
(cost=0.00..4.36 rows=102
width=8) (actual
time=0.000..1.000 rows=457
loops=1)
                                                   Index Cond: (ext_ciclo
= 2222)
                                             ->  Index Scan using
i_t_materias_en_tira__ext_alumno_en_semestre
on t_materias_en_tira
(cost=0.00..3.32 rows=12
width=41) (actual
time=0.004..0.026 rows=8
loops=457)
                                                   Index Cond:
(t_materias_en_tira.ext_alumno_en_semestre
= "outer".id)
               ->  Hash  (cost=8.77..8.77 rows=377 width=26) (actual
time=2.000..2.000 rows=0 loops=1)
                     ->  Seq Scan on t_materias  (cost=0.00..8.77 rows=377
width=26) (actual time=0.000..2.000 rows=377 loops=1)
         ->  Materialize  (cost=8.77..12.54 rows=377 width=22) (actual
time=0.000..0.163 rows=377 loops=3473)
               ->  Seq Scan on t_materias t_materias__equivalentes
(cost=0.00..8.77 rows=377 width=22) (actual
time=0.000..1.000 rows=377 loops=1)
   ->  Hash  (cost=2.07..2.07 rows=107 width=13) (actual time=0.000..0.000
rows=0 loops=1)
         ->  Seq Scan on t_grupos  (cost=0.00..2.07 rows=107 width=13)
(actual time=0.000..0.000 rows=107 loops=1)

 Total runtime: 18787.000 ms

SELECT count(*) FROM t_materias_en_tira;
 count
-------
 41059
(1 row)

SELECT count(*) FROM t_materias;
 count
-------
   377
(1 row)

SELECT version();;
                                        version
---------------------------------------------------------------------------------------
 PostgreSQL 7.4.1 on i686-pc-cygwin, compiled by GCC gcc (GCC) 3.3.1
(cygming special)
(1 row)


--
Octavio Alvarez.
E-mail: alvarezp@alvarezp.ods.org.

Agradezco que sus correos sean enviados siempre a esta dirección.



--
Octavio Alvarez.
E-mail: alvarezp@alvarezp.ods.org.

Agradezco que sus correos sean enviados siempre a esta dirección.