Re: Unexplained SQL behavior

"JOE" <joe(at)piscitella(dot)com> writes:
> I am debugging a performance problem with a view. I have narrowed down the=
> problem to when I adeed columns to my view.

Okay, I see the problem: it is in fact a bug, and one that's been around
for awhile. (Curious no one's noticed before.) When you wrote

> select distinct ... ,
> null::timestamp without time zone,
> null::timestamp without time zone, ...

the parser felt it could get away with creating only one sort column for
these two entries. This is logically a valid optimization, but it
confused later stages of the system into thinking you'd written a
DISTINCT ON clause rather than plain DISTINCT. And that suppresses an
important optimization, namely pushing down the outer query's WHERE
clause into the subselect. (You'd also find that psql's \d would
display the view definition rather oddly.)

This is a bug and I will fix it for 7.3, but in the meantime the answer
is "don't do that". I imagine this particular view definition is just a
placeholder until you get around to filling in non-null values for those
columns? The problem will go away as soon as these two view columns
aren't obviously equal. If you really need a view that works just like
this, you can work around the bug by making the null columns trivially
different, perhaps

> select distinct ... ,
> null::timestamp with time zone::timestamp without time zone,
> null::timestamp without time zone, ...

regards, tom lane