From: Tom Lane <tgl@sss.pgh.pa.us>
To: JOE <joe@piscitella.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Unexplained SQL behavior
Date: Sun, 18 Aug 2002 14:25:08 -0400
Message-ID: <24822.1029695108@sss.pgh.pa.us> (raw)
In-Reply-To: <018f01c24653$b23d8000$c801a8c0@joe>
References: <018f01c24653$b23d8000$c801a8c0@joe>
"JOE" <joe@piscitella.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
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: tgl@sss.pgh.pa.us, joe@piscitella.com
Subject: Re: Unexplained SQL behavior
In-Reply-To: <24822.1029695108@sss.pgh.pa.us>
* 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