X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 9B125D1CCD6 for ; Thu, 20 May 2004 14:46:53 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 46725-05 for ; Thu, 20 May 2004 14:46:32 -0300 (ADT) Received: from conexa.fciencias.unam.mx (conexa.fciencias.unam.mx [132.248.181.120]) by svr1.postgresql.org (Postfix) with ESMTP id B3ADBD1C9E6 for ; Thu, 20 May 2004 14:35:25 -0300 (ADT) Received: from conexa.fciencias.unam.mx (conexa.fciencias.unam.mx [127.0.0.1]) by conexa.fciencias.unam.mx (8.12.8/8.12.8) with ESMTP id i4KHXgqJ007426 for ; Thu, 20 May 2004 12:33:42 -0500 Received: (from masm@localhost) by conexa.fciencias.unam.mx (8.12.8/8.12.8/Submit) id i4KHXgJe007424; Thu, 20 May 2004 12:33:42 -0500 X-Authentication-Warning: conexa.fciencias.unam.mx: masm set sender to masm@fciencias.unam.mx using -f To: pgsql-sql@postgresql.org Subject: Simple view confuses PostgreSQL query planning From: Manuel Sugawara User-Name: Manuel Sugawara User-Mail-Address: masm@fciencias.unam.mx Date: 20 May 2004 12:33:42 -0500 Message-ID: Lines: 141 User-Agent: Gnus/5.0808 (Gnus v5.8.8) XEmacs/21.4 (Portable Code) MIME-Version: 1.0 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: quoted-printable X-Virus-Scanned: by amavisd-new at postgresql.org X-Spam-Status: No, hits=0.1 tagged_above=0.0 required=5.0 tests=RCVD_IN_RFCI X-Spam-Level: X-Archive-Number: 200405/180 X-Sequence-Number: 17894 [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 | M= odificadores ----------------------+-----------------------------+----------------------= --------------------------------- id | integer | not null default next= val('regacd.profesor_sid'::text) grupo_id | integer | not null tipo_id | "char" | not null cargo_id | integer | not null acad=E9mico_id | integer | =FAltima_actualizaci=F3n | timestamp without time zone | default now() =CDndices: "profesor_pkey" llave primaria, btree (id) "profesor_acad=E9mico" btree ("acad=E9mico_id") "profesor_grupo" btree (grupo_id) Restricciones de llave for=E1nea: "CargoTipoId" FOREIGN KEY (cargo_id, tipo_id) REFERENCES cargo(id, tipo= _id) "IdentificadorGrupoTipoId" FOREIGN KEY (grupo_id, tipo_id) REFERENCES r= egacd.grupo(id, tipo_id) "$1" FOREIGN KEY ("acad=E9mico_id") REFERENCES personal(id) Triggers: "profesor_=FAltima_actualizaci=F3n" BEFORE INSERT OR UPDATE ON regacd.p= rofesor FOR EACH ROW EXECUTE PROCEDURE "profesor_=FAltima_actualizaci=F3n"() "propaga_actualizaci=F3n_profesor" AFTER INSERT OR UPDATE ON regacd.pro= fesor FOR EACH ROW EXECUTE PROCEDURE "propaga_actualizaci=F3n_profesor"() "update_datos_acad=E9mico" BEFORE INSERT OR UPDATE ON regacd.profesor F= OR EACH ROW EXECUTE PROCEDURE "update_datos_acad=E9mico"() \d ordinario.horario Tabla "ordinario.horario" Columna | Tipo | = Modificadores ----------------------+-----------------------------+----------------------= ----------------------------------- id | integer | not null default next= val('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=E1 | boolean | not null default fa= lse hora_inicial | time without time zone | hora_final | time without time zone | sal=F3n_id | integer | nota | text | =FAltima_actualizaci=F3n | timestamp without time zone | default now() =CDndices: "horario_pkey" llave primaria, btree (id) "horario_profesor" btree (profesor_id) Restricciones de llave for=E1nea: "$2" FOREIGN KEY ("sal=F3n_id") REFERENCES "sal=F3n"(id) "$1" FOREIGN KEY (profesor_id) REFERENCES regacd.profesor(id) ON UPDATE= CASCADE ON DELETE CASCADE Triggers: "horario_=FAltima_actualizaci=F3n" BEFORE INSERT OR UPDATE ON ordinario= .horario FOR EACH ROW EXECUTE PROCEDURE "horario_=FAltima_actualizaci=F3n"() "propaga_actualizaci=F3n_horario" AFTER INSERT OR UPDATE ON ordinario.h= orario FOR EACH ROW EXECUTE PROCEDURE "propaga_actualizaci=F3n_horario"() =20 **************************************** 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 =3D p.id) where p.grupo_id IN (129314, 129315, 129316,= 129317, 129318, 129319, 129320, 129321, 129322); = QUERY PLAN=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20 ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ------------------------------------ Nested Loop Left Join (cost=3D0.00..150.79 rows=3D18 width=3D78) (actual = time=3D0.400..1.232 rows=3D19 loops=3D1) -> Index Scan using profesor_grupo, profesor_grupo, profesor_grupo, pro= fesor_grupo, profesor_grupo, profesor_grupo, profesor_grupo, profesor_grupo= , profesor_grupo on profesor p (cost=3D0.00..96.50 rows=3D18 width=3D25) (= actual time=3D0.231..0.499 rows=3D19 loops=3D1) Index Cond: ((grupo_id =3D 129314) OR (grupo_id =3D 129315) OR (gr= upo_id =3D 129316) OR (grupo_id =3D 129317) OR (grupo_id =3D 129318) OR (gr= upo_id =3D 129319) OR (grupo_id =3D 129320) OR (grupo_id =3D 129321) OR (gr= upo_id =3D 129322)) -> Index Scan using horario_profesor on horario h (cost=3D0.00..3.01 r= ows=3D1 width=3D53) (actual time=3D0.020..0.023 rows=3D1 loops=3D19) Index Cond: (h.profesor_id =3D "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=E1 | boolean | d=EDas_txt | text | hora_inicial | time without time zone | hora_final | time without time zone | hora_inicial_txt | text | hora_final_txt | text | sal=F3n_id | integer | sal=F3n_txt | text | horario_nota | text | Definici=F3n de vista: SELECT h.id AS horario_id, h.profesor_id, h.lu, h.ma, h.mi, h.ju, h.vi, h.= "s=E1", "d=EDas_atxt"(h.lu, h.ma, h.mi, h.ju, h.vi, h."s=E1") AS "d=EDas_tx= t", h.hora_inicial, h.hora_final, CASE WHEN h.hora_inicial IS NULL THEN ''::text WHEN date_part('minute'::text, h.hora_inicial) =3D 0::double pr= ecision THEN date_part('hour'::text, h.hora_inicial)::text ELSE (date_part('hour'::text, h.hora_inicial)::text || ':'::tex= t) || 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) =3D 0::double prec= ision 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=F3n_id", "sal=F3n_id_atxt"(h."sal=F3n= _id") AS "sal=F3n_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.horar= io h on (h.profesor_id =3D p.id) where p.grupo_id IN (129314, 129315, 12931= 6, 129317, 129318, 129319, 129320, 129321, 129322); = QUERY PLAN=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20 ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ------------------------------------------ Merge Left Join (cost=3D2036.01..2107.72 rows=3D18 width=3D219) (actual t= ime=3D1610.715..1611.626 rows=3D19 loops=3D1) Merge Cond: ("outer".id =3D "inner".profesor_id) -> Sort (cost=3D96.88..96.92 rows=3D18 width=3D25) (actual time=3D0.29= 9..0.325 rows=3D19 loops=3D1) Sort Key: p.id -> Index Scan using profesor_grupo, profesor_grupo, profesor_grup= o, profesor_grupo, profesor_grupo, profesor_grupo, profesor_grupo, profesor= _grupo, profesor_grupo on profesor p (cost=3D0.00..96.50 rows=3D18 width= =3D25) (actual time=3D0.062..0.220 rows=3D19 loops=3D1) Index Cond: ((grupo_id =3D 129314) OR (grupo_id =3D 129315) = OR (grupo_id =3D 129316) OR (grupo_id =3D 129317) OR (grupo_id =3D 129318) = OR (grupo_id =3D 129319) OR (grupo_id =3D 129320) OR (grupo_id =3D 129321) = OR (grupo_id =3D 129322)) -> Sort (cost=3D1939.13..1974.94 rows=3D14323 width=3D194) (actual tim= e=3D1581.038..1585.742 rows=3D13900 loops=3D1) Sort Key: h.profesor_id -> Subquery Scan h (cost=3D0.00..950.41 rows=3D14323 width=3D194= ) (actual time=3D1.180..1549.464 rows=3D14323 loops=3D1) -> Seq Scan on horario h (cost=3D0.00..936.09 rows=3D14323= width=3D45) (actual time=3D1.160..1450.191 rows=3D14323 loops=3D1) 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