[I sent this message two months ago and got no answer. I'm resending
now hoping to get some feedback.]
Hi,
I'm facing a wired problem. When I left join two tables PostgreSQL is
able to do it fast using the corresponding indices, However, if I
define a simple view (to format the data) on one of the tables, the
left join does not use the indices. Is something wrong here?
The two tables:
****************************************
\d regacd.profesor
Tabla "regacd.profesor"
Columna | Tipo | Modificadores
----------------------+-----------------------------+-------------------------------------------------------
id | integer | not null default
nextval('regacd.profesor_sid'::text)
grupo_id | integer | not null
tipo_id | "char" | not null
cargo_id | integer | not null
académico_id | integer |
última_actualización | timestamp without time zone | default now()
Índices:
"profesor_pkey" llave primaria, btree (id)
"profesor_académico" btree ("académico_id")
"profesor_grupo" btree (grupo_id)
Restricciones de llave foránea:
"CargoTipoId" FOREIGN KEY (cargo_id, tipo_id) REFERENCES cargo(id, tipo_id)
"IdentificadorGrupoTipoId" FOREIGN KEY (grupo_id, tipo_id) REFERENCES
regacd.grupo(id, tipo_id)
"$1" FOREIGN KEY ("académico_id") REFERENCES personal(id)
Triggers:
"profesor_última_actualización" BEFORE INSERT OR UPDATE ON regacd.profesor FOR
EACH ROW EXECUTE PROCEDURE "profesor_última_actualización"()
"propaga_actualización_profesor" AFTER INSERT OR UPDATE ON regacd.profesor FOR
EACH ROW EXECUTE PROCEDURE "propaga_actualización_profesor"()
"update_datos_académico" BEFORE INSERT OR UPDATE ON regacd.profesor FOR EACH ROW
EXECUTE PROCEDURE "update_datos_académico"()
\d ordinario.horario
Tabla "ordinario.horario"
Columna | Tipo |
Modificadores
----------------------+-----------------------------+---------------------------------------------------------
id | integer | not null default
nextval('ordinario.horario_sid'::text)
profesor_id | integer |
lu | boolean | not null default false
ma | boolean | not null default false
mi | boolean | not null default false
ju | boolean | not null default false
vi | boolean | not null default false
sá | boolean | not null default false
hora_inicial | time without time zone |
hora_final | time without time zone |
salón_id | integer |
nota | text |
última_actualización | timestamp without time zone | default now()
Índices:
"horario_pkey" llave primaria, btree (id)
"horario_profesor" btree (profesor_id)
Restricciones de llave foránea:
"$2" FOREIGN KEY ("salón_id") REFERENCES "salón"(id)
"$1" FOREIGN KEY (profesor_id) REFERENCES regacd.profesor(id) ON UPDATE CASCADE ON
DELETE CASCADE
Triggers:
"horario_última_actualización" BEFORE INSERT OR UPDATE ON ordinario.horario FOR
EACH ROW EXECUTE PROCEDURE "horario_última_actualización"()
"propaga_actualización_horario" AFTER INSERT OR UPDATE ON ordinario.horario FOR
EACH ROW EXECUTE PROCEDURE "propaga_actualización_horario"()
****************************************
Now, a left join query of the tables leads a nice and fast plan:
explain analyze select * from regacd.profesor p left join ordinario.horario h on
(h.profesor_id = p.id) where p.grupo_id IN (129314, 129315, 129316, 129317, 129318,
129319, 129320, 129321, 129322);
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Nested Loop Left Join (cost=0.00..150.79 rows=18 width=78) (actual time=0.400..1.232
rows=19 loops=1)
-> Index Scan using profesor_grupo, profesor_grupo, profesor_grupo,
profesor_grupo, profesor_grupo, profesor_grupo, profesor_grupo, profesor_grupo,
profesor_grupo on profesor p (cost=0.00..96.50 rows=18 width=25) (actual
time=0.231..0.499 rows=19 loops=1)
Index Cond: ((grupo_id = 129314) OR (grupo_id = 129315) OR (grupo_id =
129316) OR (grupo_id = 129317) OR (grupo_id = 129318) OR (grupo_id = 129319) OR
(grupo_id = 129320) OR (grupo_id = 129321) OR (grupo_id = 129322))
-> Index Scan using horario_profesor on horario h (cost=0.00..3.01 rows=1
width=53) (actual time=0.020..0.023 rows=1 loops=19)
Index Cond: (h.profesor_id = "outer".id)
Total runtime: 1.542 ms
(6 filas)
However, if I define a simple view on ordinario.horario
\d vordinario.horario
Vista "vordinario.horario"
Columna | Tipo | Modificadores
------------------+------------------------+---------------
horario_id | integer |
profesor_id | integer |
lu | boolean |
ma | boolean |
mi | boolean |
ju | boolean |
vi | boolean |
sá | boolean |
días_txt | text |
hora_inicial | time without time zone |
hora_final | time without time zone |
hora_inicial_txt | text |
hora_final_txt | text |
salón_id | integer |
salón_txt | text |
horario_nota | text |
Definición de vista:
SELECT h.id AS horario_id, h.profesor_id, h.lu, h.ma, h.mi, h.ju, h.vi, h."sá",
"días_atxt"(h.lu, h.ma, h.mi, h.ju, h.vi, h."sá") AS "días_txt", h.hora_inicial,
h.hora_final,
CASE
WHEN h.hora_inicial IS NULL THEN ''::text
WHEN date_part('minute'::text, h.hora_inicial) = 0::double precision THEN
date_part('hour'::text, h.hora_inicial)::text
ELSE (date_part('hour'::text, h.hora_inicial)::text || ':'::text) ||
to_char(date_part('minute'::text, h.hora_inicial), 'fm00'::text)
END AS hora_inicial_txt,
CASE
WHEN h.hora_final IS NULL THEN ''::text
WHEN date_part('minute'::text, h.hora_final) = 0::double precision THEN
date_part('hour'::text, h.hora_final)::text
ELSE (date_part('hour'::text, h.hora_final)::text || ':'::text) ||
to_char(date_part('minute'::text, h.hora_final), 'fm00'::text)
END AS hora_final_txt, h."salón_id", "salón_id_atxt"(h."salón_id") AS
"salón_txt", h.nota AS horario_nota
FROM ordinario.horario h;
The left join gives a secuential scan, no matter what:
explain analyze select * from regacd.profesor p left join vordinario.horario h on
(h.profesor_id = p.id) where p.grupo_id IN (129314, 129315, 129316, 129317, 129318,
129319, 129320, 129321, 129322);
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Merge Left Join (cost=2036.01..2107.72 rows=18 width=219) (actual
time=1610.715..1611.626 rows=19 loops=1)
Merge Cond: ("outer".id = "inner".profesor_id)
-> Sort (cost=96.88..96.92 rows=18 width=25) (actual time=0.299..0.325 rows=19
loops=1)
Sort Key: p.id
-> Index Scan using profesor_grupo, profesor_grupo, profesor_grupo,
profesor_grupo, profesor_grupo, profesor_grupo, profesor_grupo, profesor_grupo,
profesor_grupo on profesor p (cost=0.00..96.50 rows=18 width=25) (actual
time=0.062..0.220 rows=19 loops=1)
Index Cond: ((grupo_id = 129314) OR (grupo_id = 129315) OR (grupo_id =
129316) OR (grupo_id = 129317) OR (grupo_id = 129318) OR (grupo_id = 129319) OR
(grupo_id = 129320) OR (grupo_id = 129321) OR (grupo_id = 129322))
-> Sort (cost=1939.13..1974.94 rows=14323 width=194) (actual
time=1581.038..1585.742 rows=13900 loops=1)
Sort Key: h.profesor_id
-> Subquery Scan h (cost=0.00..950.41 rows=14323 width=194) (actual
time=1.180..1549.464 rows=14323 loops=1)
-> Seq Scan on horario h (cost=0.00..936.09 rows=14323 width=45)
(actual time=1.160..1450.191 rows=14323 loops=1)
Total runtime: 1616.958 ms
(11 filas)
Regards,
Manuel.
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
