pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedSimple view confuses PostgreSQL query planning
3+ messages / 2 participants
[nested] [flat]
* Simple view confuses PostgreSQL query planning
@ 2004-05-20 17:33 Manuel Sugawara <masm@fciencias.unam.mx>
2004-05-20 19:06 ` Re: Simple view confuses PostgreSQL query planning Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 3+ messages in thread
From: Manuel Sugawara @ 2004-05-20 17:33 UTC (permalink / raw)
To: pgsql-sql
[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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Simple view confuses PostgreSQL query planning
2004-05-20 17:33 Simple view confuses PostgreSQL query planning Manuel Sugawara <masm@fciencias.unam.mx>
@ 2004-05-20 19:06 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-05-20 21:43 ` Re: Simple view confuses PostgreSQL query planning Manuel Sugawara <masm@fciencias.unam.mx>
0 siblings, 1 reply; 3+ messages in thread
From: Tom Lane @ 2004-05-20 19:06 UTC (permalink / raw)
To: Manuel Sugawara <masm@fciencias.unam.mx>; +Cc: pgsql-sql
Manuel Sugawara <masm@fciencias.unam.mx> writes:
> 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?
> 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;
I think the issue is that the subquery isn't getting flattened, because
of this test:
/*
* If we are inside an outer join, only pull up subqueries whose
* targetlists are nullable --- otherwise substituting their tlist
* entries for upper Var references would do the wrong thing (the
* results wouldn't become NULL when they're supposed to).
*
* XXX This could be improved by generating pseudo-variables for
* such expressions; we'd have to figure out how to get the pseudo-
* variables evaluated at the right place in the modified plan
* tree. Fix it someday.
*/
if (...
(!below_outer_join || has_nullable_targetlist(subquery)))
has_nullable_targetlist() is returning false because of the CASE
expressions. Its analysis could be more detailed, but in point of fact
with this particular definition the targetlist *isn't* nullable ---
the first arm of each CASE will yield a non-null result for null input.
Get rid of the CASEs (perhaps you could wrap them into functions
declared STRICT) and the view would be flattenable.
The reason we need this is shown in this old bug report:
http://archives.postgresql.org/pgsql-bugs/2001-04/msg00223.php
If the view did get flattened then the CASE outputs would give
wrong answers --- nonnull when they should be null --- just as Victor
described for constants.
The general fix mentioned in the comment is still a long way off.
regards, tom lane
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Simple view confuses PostgreSQL query planning
2004-05-20 17:33 Simple view confuses PostgreSQL query planning Manuel Sugawara <masm@fciencias.unam.mx>
2004-05-20 19:06 ` Re: Simple view confuses PostgreSQL query planning Tom Lane <tgl@sss.pgh.pa.us>
@ 2004-05-20 21:43 ` Manuel Sugawara <masm@fciencias.unam.mx>
0 siblings, 0 replies; 3+ messages in thread
From: Manuel Sugawara @ 2004-05-20 21:43 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
Tom Lane <tgl@sss.pgh.pa.us> writes:
> Get rid of the CASEs (perhaps you could wrap them into functions
> declared STRICT) and the view would be flattenable.
Will do that. Thanks.
Regards,
Manuel.
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2004-05-20 21:43 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-05-20 17:33 Simple view confuses PostgreSQL query planning Manuel Sugawara <masm@fciencias.unam.mx>
2004-05-20 19:06 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-05-20 21:43 ` Manuel Sugawara <masm@fciencias.unam.mx>
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