pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedPerformance inside and outside view ( WAS Re: Select the max on a field )
6+ messages / 3 participants
[nested] [flat]
* Performance inside and outside view ( WAS Re: Select the max on a field )
@ 2002-09-12 14:31 Gaetano Mendola <mendola@bigfoot.com>
0 siblings, 1 reply; 6+ messages in thread
From: Gaetano Mendola @ 2002-09-12 14:31 UTC (permalink / raw)
To: pgsql-sql
"Stephan Szabo" <sszabo@megazone23.bigpanda.com> wrote in message
news:20020912065516.L45340-100000@megazone23.bigpanda.com...
> select distinct on (att_2) * from test
> order by att_2, att_1 desc;
Yes that's right it's help me,
but here the optimizer have some problems:
CREATE VIEW last_user_logs AS
SELECT DISTINCT ON (id_user) *
FROM user_logs
ORDER BY id_user, id_user_log DESC;
and now:
# explain analyze select * from user_logs where id_user = '5430';
NOTICE: QUERY PLAN:
Index Scan using idx_user_user_logs on user_logs (cost=0.00..3038.59
rows=939 width=68) (actual time=0.03..0.03 rows=0 loops=1)
Total runtime: 0.12 msec
EXPLAIN
empdb=# explain analyze select * from last_user_logs where id_user = 5430;
NOTICE: QUERY PLAN:
Subquery Scan last_user_logs (cost=20256.12..20725.43 rows=18772 width=68)
(actual time=3526.10..3526.10 rows=0 loops=1)
-> Unique (cost=20256.12..20725.43 rows=18772 width=68) (actual
time=3067.14..3522.54 rows=2226 loops=1)
-> Sort (cost=20256.12..20256.12 rows=187723 width=68) (actual
time=3067.13..3241.94 rows=187723 loops=1)
-> Seq Scan on user_logs (cost=0.00..3813.23 rows=187723
width=68) (actual time=0.02..1070.59 rows=187723 loops=1)
Total runtime: 3578.07 msec
EXPLAIN
if I do instead without view and
#explain analyze SELECT DISTINCT ON (id_user) *
FROM user_logs
WHERE id_user = 5430
ORDER BY id_user, id_user_log DESC;
NOTICE: QUERY PLAN:
Unique (cost=3084.93..3087.27 rows=94 width=68) (actual time=0.21..0.21
rows=0 loops=1)
-> Sort (cost=3084.93..3084.93 rows=939 width=68) (actual
time=0.21..0.21 rows=0 loops=1)
-> Index Scan using idx_user_user_logs on user_logs
(cost=0.00..3038.59 rows=939 width=68) (actual time=0.03..0.03 rows=0
loops=1)
Total runtime: 0.31 msec
EXPLAIN
Ciao
Gaetano
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Performance inside and outside view ( WAS Re: Select the max on a field )
@ 2002-09-12 14:50 Tom Lane <tgl@sss.pgh.pa.us>
parent: Gaetano Mendola <mendola@bigfoot.com>
0 siblings, 2 replies; 6+ messages in thread
From: Tom Lane @ 2002-09-12 14:50 UTC (permalink / raw)
To: Gaetano Mendola <mendola@bigfoot.com>; +Cc: pgsql-sql
"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 ...
regards, tom lane
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Performance inside and outside view ( WAS Re: Select the
@ 2002-09-12 15:22 Stephan Szabo <sszabo@megazone23.bigpanda.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 6+ messages in thread
From: Stephan Szabo @ 2002-09-12 15:22 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Gaetano Mendola <mendola@bigfoot.com>; pgsql-sql
On Thu, 12 Sep 2002, Tom Lane wrote:
> "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 ...
At least in current sources it would, since it can't push down the clause
(unless I'm misreading subquery_is_pushdown_safe and the comment) through
the distinct on.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Performance inside and outside view ( WAS Re: Select the max on a field )
@ 2002-09-12 15:53 Gaetano Mendola <mendola@bigfoot.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 6+ messages in thread
From: Gaetano Mendola @ 2002-09-12 15:53 UTC (permalink / raw)
To: pgsql-sql
"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.
Ciao
Gaetano
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Performance inside and outside view ( WAS Re: Select the
@ 2002-09-12 16:05 Stephan Szabo <sszabo@megazone23.bigpanda.com>
parent: Gaetano Mendola <mendola@bigfoot.com>
0 siblings, 0 replies; 6+ messages in thread
From: Stephan Szabo @ 2002-09-12 16:05 UTC (permalink / raw)
To: Gaetano Mendola <mendola@bigfoot.com>; +Cc: pgsql-sql
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.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Performance inside and outside view ( WAS Re: Select the
@ 2002-09-12 18:57 Tom Lane <tgl@sss.pgh.pa.us>
parent: Stephan Szabo <sszabo@megazone23.bigpanda.com>
0 siblings, 0 replies; 6+ messages in thread
From: Tom Lane @ 2002-09-12 18:57 UTC (permalink / raw)
To: Stephan Szabo <sszabo@megazone23.bigpanda.com>; +Cc: Gaetano Mendola <mendola@bigfoot.com>; pgsql-sql
Stephan Szabo <sszabo@megazone23.bigpanda.com> writes:
> On Thu, 12 Sep 2002, Tom Lane wrote:
>> 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 ...
> At least in current sources it would, since it can't push down the clause
> (unless I'm misreading subquery_is_pushdown_safe and the comment) through
> the distinct on.
Oh, duh, you're right; I'd missed the DISTINCT ON ...
regards, tom lane
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2002-09-12 18:57 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-09-12 14:31 Performance inside and outside view ( WAS Re: Select the max on a field ) Gaetano Mendola <mendola@bigfoot.com>
2002-09-12 14:50 ` Tom Lane <tgl@sss.pgh.pa.us>
2002-09-12 15:22 ` Re: Performance inside and outside view ( WAS Re: Select the Stephan Szabo <sszabo@megazone23.bigpanda.com>
2002-09-12 18:57 ` Re: Performance inside and outside view ( WAS Re: Select the Tom Lane <tgl@sss.pgh.pa.us>
2002-09-12 15:53 ` Gaetano Mendola <mendola@bigfoot.com>
2002-09-12 16:05 ` Re: Performance inside and outside view ( WAS Re: Select the Stephan Szabo <sszabo@megazone23.bigpanda.com>
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