From: Manuel Sugawara <masm@fciencias.unam.mx>
To: pgsql-sql@postgresql.org
Subject: Simple view confuses PostgreSQL query planning
Date: 20 May 2004 12:33:42 -0500
Message-ID: <m365ar6m15.fsf@conexa.fciencias.unam.mx> (raw)
[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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: masm@fciencias.unam.mx
Subject: Re: Simple view confuses PostgreSQL query planning
In-Reply-To: <m365ar6m15.fsf@conexa.fciencias.unam.mx>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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