To: Manuel Sugawara <masm@fciencias.unam.mx>
Cc: pgsql-sql@postgresql.org
Subject: Re: Simple view confuses PostgreSQL query planning 
In-reply-to: <m365ar6m15.fsf@conexa.fciencias.unam.mx> 
References: <m365ar6m15.fsf@conexa.fciencias.unam.mx>
Comments: In-reply-to Manuel Sugawara <masm@fciencias.unam.mx>
	message dated "20 May 2004 12:33:42 -0500"
Date: Thu, 20 May 2004 15:06:23 -0400
Message-ID: <3948.1085079983@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

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