pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedleft join on a view takes significantly more time.
2+ messages / 2 participants
[nested] [flat]
* left join on a view takes significantly more time.
@ 2004-03-31 18:55 Manuel Sugawara <masm@fciencias.unam.mx>
0 siblings, 1 reply; 2+ messages in thread
From: Manuel Sugawara @ 2004-03-31 18:55 UTC (permalink / raw)
To: pgsql-sql
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.
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: left join on a view takes significantly more time.
@ 2004-03-31 23:49 Josh Berkus <josh@agliodbs.com>
parent: Manuel Sugawara <masm@fciencias.unam.mx>
0 siblings, 0 replies; 2+ messages in thread
From: Josh Berkus @ 2004-03-31 23:49 UTC (permalink / raw)
To: Manuel Sugawara <masm@fciencias.unam.mx>; pgsql-sql
Manuel,
> 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?
At a guess, the complex CASE statements are causing the planner to behave
badly. What happens if you do a straight join and not a left join?
Also, it's generally a good idea to put your join expression in the same order
as the referenced tables. You confused me and you could confuse the planner
at some stage.
--
-Josh Berkus
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2004-03-31 23:49 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-03-31 18:55 left join on a view takes significantly more time. Manuel Sugawara <masm@fciencias.unam.mx>
2004-03-31 23:49 ` Josh Berkus <josh@agliodbs.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox