Date: Thu, 12 Sep 2002 09:05:26 -0700 (PDT)
From: Stephan Szabo <sszabo@megazone23.bigpanda.com>
To: Gaetano Mendola <mendola@bigfoot.com>
Cc: <pgsql-sql@postgresql.org>
Subject: Re: Performance inside and outside view ( WAS Re: Select the
In-Reply-To: <alqd9s$mlb$1@news.hub.org>
Message-ID: <20020912090208.Q46162-100000@megazone23.bigpanda.com>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII

On Thu, 12 Sep 2002, Gaetano Mendola wrote:

>
> "Tom Lane" <tgl@sss.pgh.pa.us> wrote in message
> news:7350.1031842223@sss.pgh.pa.us...
> > "Gaetano Mendola" <mendola@bigfoot.com> writes:
> > > Yes that's right it's help me,
> > > but here the optimizer have some problems:
> >
> > What's the datatype of id_user, and why are you quoting the compared
> > constant in some cases but not others?  I don't think the difference
> > has anything to do with the view here ...
>
> id_user is an INTEGER type.
>
> Well what about this two selects:
>
> a) SELECT DISTINCT ON (id_user) *
>     FROM user_logs
>    WHERE id_user = 5430
>    ORDER BY id_user, id_user_log DESC;
>
> b) CREATE VIEW last_user_logs AS
>      SELECT DISTINCT ON (id_user) *
>      FROM user_logs
>      ORDER BY id_user, id_user_log DESC;
>
>      SELECT *
>      FROM last_user_logs
>      WHERE id_user = 5430
>
>
> I think that in the second case the filter is not pushed inside the view.

Pushing into distinct on has limited cases where it's legal,
you're using one of them, but the system doesn't differentiate
and doesn't push in either.

 select distinct on (col1) * from table where col2=2;
and
 select * from (select distinct on (col1) * from table) v
  where col2=2; -- this is like a view case
are not the same query.

I believe Tom makes a comment to the effect that it should
be safe if the clause only references distinct on'd columns.