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
Date: Thu, 12 Sep 2002 09:05:26 -0700 (PDT)
Message-ID: <20020912090208.Q46162-100000@megazone23.bigpanda.com> (raw)
In-Reply-To: <alqd9s$mlb$1@news.hub.org>
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.
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: sszabo@megazone23.bigpanda.com, mendola@bigfoot.com
Subject: Re: Performance inside and outside view ( WAS Re: Select the
In-Reply-To: <20020912090208.Q46162-100000@megazone23.bigpanda.com>
* 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