pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedUseless sort by
19+ messages / 9 participants
[nested] [flat]
* Useless sort by
@ 2010-09-13 09:47 Gaetano Mendola <mendola@gmail.com>
2010-09-13 14:44 ` Re: Useless sort by Tom Lane <tgl@sss.pgh.pa.us>
2010-09-14 16:15 ` Re: Useless sort by Dave Crooke <dcrooke@gmail.com>
0 siblings, 2 replies; 19+ messages in thread
From: Gaetano Mendola @ 2010-09-13 09:47 UTC (permalink / raw)
To: pgsql-performance
Hi all,
I have a view v_table defined as following:
select a,b,c,d,e,f
from t_table
sort by a,b,c;
the usage pattern of this view is the following:
select distinct(a) from v_table;
select distinct(b) from v_table where a = "XXX";
select distinct(c) from v_table where a = "XXX" and b = "YYYY";
because of that sort in the view definition the first query above
takes not less than 3 seconds. I have solved this performance issue
removing the sort from the view definition and putting it in the
select reducing the time from > 3secons to < 150ms.
Can not the optimizer take rid of that useless sort on those
kind of queries ?
Regards
Gaetano Mendola
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: Useless sort by
2010-09-13 09:47 Useless sort by Gaetano Mendola <mendola@gmail.com>
@ 2010-09-13 14:44 ` Tom Lane <tgl@sss.pgh.pa.us>
2010-09-13 15:55 ` Re: Useless sort by Gaetano Mendola <mendola@gmail.com>
1 sibling, 1 reply; 19+ messages in thread
From: Tom Lane @ 2010-09-13 14:44 UTC (permalink / raw)
To: Gaetano Mendola <mendola@gmail.com>; +Cc: pgsql-performance
Gaetano Mendola <mendola@gmail.com> writes:
> because of that sort in the view definition the first query above
> takes not less than 3 seconds. I have solved this performance issue
> removing the sort from the view definition and putting it in the
> select reducing the time from > 3secons to < 150ms.
> Can not the optimizer take rid of that useless sort on those
> kind of queries ?
It is not the optimizer's job to second-guess the user on whether a sort
is really needed there. If we did make it throw away non-top-level
sorts, we'd have hundreds of users screaming loudly.
regards, tom lane
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: Useless sort by
2010-09-13 09:47 Useless sort by Gaetano Mendola <mendola@gmail.com>
2010-09-13 14:44 ` Re: Useless sort by Tom Lane <tgl@sss.pgh.pa.us>
@ 2010-09-13 15:55 ` Gaetano Mendola <mendola@gmail.com>
2010-09-13 16:48 ` Re: Useless sort by Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 19+ messages in thread
From: Gaetano Mendola @ 2010-09-13 15:55 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>
On 09/13/2010 04:44 PM, Tom Lane wrote:
> Gaetano Mendola <mendola@gmail.com> writes:
>> because of that sort in the view definition the first query above
>> takes not less than 3 seconds. I have solved this performance issue
>> removing the sort from the view definition and putting it in the
>> select reducing the time from > 3secons to < 150ms.
>
>> Can not the optimizer take rid of that useless sort on those
>> kind of queries ?
>
> It is not the optimizer's job to second-guess the user on whether a sort
> is really needed there. If we did make it throw away non-top-level
> sorts, we'd have hundreds of users screaming loudly.
Of course I'm not suggesting to take away the "sort by" and give the user
an unsorted result, I'm asking why the the optimizer in cases like:
select unique(a) from v_table_with_order_by;
doesn't takes away the "order by" inside the view and puts it back "rewriting the
query like this:
select unique(a) from v_table_without_order_by
order by a;
then the user will not know about it. The result is the same but 30 times
faster (in my case).
Regards
Gaetano Mendola
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: Useless sort by
2010-09-13 09:47 Useless sort by Gaetano Mendola <mendola@gmail.com>
2010-09-13 14:44 ` Re: Useless sort by Tom Lane <tgl@sss.pgh.pa.us>
2010-09-13 15:55 ` Re: Useless sort by Gaetano Mendola <mendola@gmail.com>
@ 2010-09-13 16:48 ` Tom Lane <tgl@sss.pgh.pa.us>
2010-09-13 17:09 ` Re: Useless sort by Gaetano Mendola <mendola@gmail.com>
2010-09-14 07:10 ` Re: Useless sort by Heikki Linnakangas <heikki.linnakangas@enterprisedb.com>
0 siblings, 2 replies; 19+ messages in thread
From: Tom Lane @ 2010-09-13 16:48 UTC (permalink / raw)
To: Gaetano Mendola <mendola@gmail.com>; +Cc: pgsql-performance
Gaetano Mendola <mendola@gmail.com> writes:
> Of course I'm not suggesting to take away the "sort by" and give the user
> an unsorted result, I'm asking why the the optimizer in cases like:
> select unique(a) from v_table_with_order_by;
> doesn't takes away the "order by" inside the view and puts it back "rewriting the
> query like this:
> select unique(a) from v_table_without_order_by
> order by a;
That changes the order in which the rows are fed to unique(a). The
principal real-world use for a non-top-level ORDER BY is exactly to
determine the order in which rows are fed to a function, so we will
have a revolt on our hands if we break that.
regards, tom lane
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: Useless sort by
2010-09-13 09:47 Useless sort by Gaetano Mendola <mendola@gmail.com>
2010-09-13 14:44 ` Re: Useless sort by Tom Lane <tgl@sss.pgh.pa.us>
2010-09-13 15:55 ` Re: Useless sort by Gaetano Mendola <mendola@gmail.com>
2010-09-13 16:48 ` Re: Useless sort by Tom Lane <tgl@sss.pgh.pa.us>
@ 2010-09-13 17:09 ` Gaetano Mendola <mendola@gmail.com>
2010-09-13 17:24 ` Re: Useless sort by Maciek Sakrejda <msakrejda@truviso.com>
2010-09-13 19:08 ` Re: Useless sort by Scott Marlowe <scott.marlowe@gmail.com>
2010-09-23 00:54 ` Re: Useless sort by Robert Haas <robertmhaas@gmail.com>
1 sibling, 3 replies; 19+ messages in thread
From: Gaetano Mendola @ 2010-09-13 17:09 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance
On Mon, Sep 13, 2010 at 6:48 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Gaetano Mendola <mendola@gmail.com> writes:
>> Of course I'm not suggesting to take away the "sort by" and give the user
>> an unsorted result, I'm asking why the the optimizer in cases like:
>
>> select unique(a) from v_table_with_order_by;
>
>> doesn't takes away the "order by" inside the view and puts it back "rewriting the
>> query like this:
>
>> select unique(a) from v_table_without_order_by
>> order by a;
>
> That changes the order in which the rows are fed to unique(a). The
> principal real-world use for a non-top-level ORDER BY is exactly to
> determine the order in which rows are fed to a function, so we will
> have a revolt on our hands if we break that.
I see your point, but some functions like: unique, count are not affected
by the order of values fed, and I don't think either that unique has to
give out the unique values in the same fed order.
Regards
Gaetano Mendola
--
cpp-today.blogspot.com
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: Useless sort by
2010-09-13 09:47 Useless sort by Gaetano Mendola <mendola@gmail.com>
2010-09-13 14:44 ` Re: Useless sort by Tom Lane <tgl@sss.pgh.pa.us>
2010-09-13 15:55 ` Re: Useless sort by Gaetano Mendola <mendola@gmail.com>
2010-09-13 16:48 ` Re: Useless sort by Tom Lane <tgl@sss.pgh.pa.us>
2010-09-13 17:09 ` Re: Useless sort by Gaetano Mendola <mendola@gmail.com>
@ 2010-09-13 17:24 ` Maciek Sakrejda <msakrejda@truviso.com>
2 siblings, 0 replies; 19+ messages in thread
From: Maciek Sakrejda @ 2010-09-13 17:24 UTC (permalink / raw)
To: Gaetano Mendola <mendola@gmail.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-performance
> I see your point, but some functions like: unique, count are not affected
> by the order of values fed, and I don't think either that unique has to
> give out the unique values in the same fed order.
Sure. You'd need additional metadata about which aggregates care about
sort order and which don't. Our system is more sensitive to this sort
of thing and so we've actually implemented this, but in the absence of
this "order-sensitive" flag, you have to assume sorts matter (or
you're leaving a *lot* of room for shooting yourself in the foot).
Even with this, it seems a little dodgy to mess up sort order in a
top-level query. Relational databases are ostensibly relational, but I
imagine in practice, it may be a toss-up in the trade-off between the
performance benefits of what you are suggesting and the breaking of
implicit non-relational behaviors that users have been taking for
granted.
---
Maciek Sakrejda | System Architect | Truviso
1065 E. Hillsdale Blvd., Suite 215
Foster City, CA 94404
(650) 242-3500 Main
www.truviso.com
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: Useless sort by
2010-09-13 09:47 Useless sort by Gaetano Mendola <mendola@gmail.com>
2010-09-13 14:44 ` Re: Useless sort by Tom Lane <tgl@sss.pgh.pa.us>
2010-09-13 15:55 ` Re: Useless sort by Gaetano Mendola <mendola@gmail.com>
2010-09-13 16:48 ` Re: Useless sort by Tom Lane <tgl@sss.pgh.pa.us>
2010-09-13 17:09 ` Re: Useless sort by Gaetano Mendola <mendola@gmail.com>
@ 2010-09-13 19:08 ` Scott Marlowe <scott.marlowe@gmail.com>
2 siblings, 0 replies; 19+ messages in thread
From: Scott Marlowe @ 2010-09-13 19:08 UTC (permalink / raw)
To: Gaetano Mendola <mendola@gmail.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-performance
On Mon, Sep 13, 2010 at 11:09 AM, Gaetano Mendola <mendola@gmail.com> wrote:
> On Mon, Sep 13, 2010 at 6:48 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> Gaetano Mendola <mendola@gmail.com> writes:
>>> Of course I'm not suggesting to take away the "sort by" and give the user
>>> an unsorted result, I'm asking why the the optimizer in cases like:
>>
>>> select unique(a) from v_table_with_order_by;
>>
>>> doesn't takes away the "order by" inside the view and puts it back "rewriting the
>>> query like this:
>>
>>> select unique(a) from v_table_without_order_by
>>> order by a;
>>
>> That changes the order in which the rows are fed to unique(a). The
>> principal real-world use for a non-top-level ORDER BY is exactly to
>> determine the order in which rows are fed to a function, so we will
>> have a revolt on our hands if we break that.
>
> I see your point, but some functions like: unique, count are not affected
> by the order of values fed, and I don't think either that unique has to
> give out the unique values in the same fed order.
First off, having a top level order by in a view is considered poor
practice. It adds an overhead you may or may not need each time the
view is accessed, and there's no simple way to avoid it once it's in
there.
On top of that you'd be adding complexity to the planner that would
make it slower and more likely to make mistakes, all to fix a problem
that I and most others don't have.
--
To understand recursion, one must first understand recursion.
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: Useless sort by
2010-09-13 09:47 Useless sort by Gaetano Mendola <mendola@gmail.com>
2010-09-13 14:44 ` Re: Useless sort by Tom Lane <tgl@sss.pgh.pa.us>
2010-09-13 15:55 ` Re: Useless sort by Gaetano Mendola <mendola@gmail.com>
2010-09-13 16:48 ` Re: Useless sort by Tom Lane <tgl@sss.pgh.pa.us>
2010-09-13 17:09 ` Re: Useless sort by Gaetano Mendola <mendola@gmail.com>
@ 2010-09-23 00:54 ` Robert Haas <robertmhaas@gmail.com>
2010-09-23 03:05 ` Re: Useless sort by gnuoytr@rcn.com
2 siblings, 1 reply; 19+ messages in thread
From: Robert Haas @ 2010-09-23 00:54 UTC (permalink / raw)
To: Gaetano Mendola <mendola@gmail.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-performance
On Mon, Sep 13, 2010 at 1:09 PM, Gaetano Mendola <mendola@gmail.com> wrote:
> I see your point, but some functions like: unique, count are not affected
> by the order of values fed, and I don't think either that unique has to
> give out the unique values in the same fed order.
Gee, I'd sure expect it to.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: Useless sort by
2010-09-13 09:47 Useless sort by Gaetano Mendola <mendola@gmail.com>
2010-09-13 14:44 ` Re: Useless sort by Tom Lane <tgl@sss.pgh.pa.us>
2010-09-13 15:55 ` Re: Useless sort by Gaetano Mendola <mendola@gmail.com>
2010-09-13 16:48 ` Re: Useless sort by Tom Lane <tgl@sss.pgh.pa.us>
2010-09-13 17:09 ` Re: Useless sort by Gaetano Mendola <mendola@gmail.com>
2010-09-23 00:54 ` Re: Useless sort by Robert Haas <robertmhaas@gmail.com>
@ 2010-09-23 03:05 ` gnuoytr@rcn.com
2010-09-23 04:01 ` Re: Useless sort by Greg Smith <greg@2ndquadrant.com>
2010-09-23 04:46 ` Re: Useless sort by Robert Haas <robertmhaas@gmail.com>
2010-10-25 10:09 ` Re: Useless sort by Gaetano Mendola <mendola@gmail.com>
0 siblings, 3 replies; 19+ messages in thread
From: gnuoytr@rcn.com @ 2010-09-23 03:05 UTC (permalink / raw)
To: pgsql-performance
---- Original message ----
>Date: Wed, 22 Sep 2010 20:54:22 -0400
>From: pgsql-performance-owner@postgresql.org (on behalf of Robert Haas <robertmhaas@gmail.com>)
>Subject: Re: [PERFORM] Useless sort by
>To: Gaetano Mendola <mendola@gmail.com>
>Cc: Tom Lane <tgl@sss.pgh.pa.us>,pgsql-performance@postgresql.org
>
>On Mon, Sep 13, 2010 at 1:09 PM, Gaetano Mendola <mendola@gmail.com> wrote:
>> I see your point, but some functions like: unique, count are not affected
>> by the order of values fed, and I don't think either that unique has to
>> give out the unique values in the same fed order.
>
>Gee, I'd sure expect it to.
Spoken like a dyed in the wool COBOL coder. The RM has no need for order; it's set based. I've dabbled in PG for some time, and my sense is increasingly that PG developers are truly code oriented, not database (set) oriented.
robert
>
>--
>Robert Haas
>EnterpriseDB: http://www.enterprisedb.com
>The Enterprise Postgres Company
>
>--
>Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
>To make changes to your subscription:
>http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: Useless sort by
2010-09-13 09:47 Useless sort by Gaetano Mendola <mendola@gmail.com>
2010-09-13 14:44 ` Re: Useless sort by Tom Lane <tgl@sss.pgh.pa.us>
2010-09-13 15:55 ` Re: Useless sort by Gaetano Mendola <mendola@gmail.com>
2010-09-13 16:48 ` Re: Useless sort by Tom Lane <tgl@sss.pgh.pa.us>
2010-09-13 17:09 ` Re: Useless sort by Gaetano Mendola <mendola@gmail.com>
2010-09-23 00:54 ` Re: Useless sort by Robert Haas <robertmhaas@gmail.com>
2010-09-23 03:05 ` Re: Useless sort by gnuoytr@rcn.com
@ 2010-09-23 04:01 ` Greg Smith <greg@2ndquadrant.com>
2010-09-23 04:18 ` Re: Useless sort by Scott Marlowe <scott.marlowe@gmail.com>
2 siblings, 1 reply; 19+ messages in thread
From: Greg Smith @ 2010-09-23 04:01 UTC (permalink / raw)
To: gnuoytr@rcn.com; +Cc: pgsql-performance
gnuoytr@rcn.com wrote:
> Spoken like a dyed in the wool COBOL coder. The RM has no need for order; it's set based. I've dabbled in PG for some time, and my sense is increasingly that PG developers are truly code oriented, not database (set) oriented.
>
I can't tell if you meant for this to be insulting or my reading it that
way is wrong, but it certainly wasn't put in a helpful tone. Let me
summarize for you. You've been told that putting ORDER BY into a view
is a generally poor idea anyway, that it's better to find ways avoid
this class of concern altogether. There are significant non-obvious
technical challenges behind actually implementing the behavior you'd
like to see; the concerns raised by Tom and Maciek make your idea
impractical even if it were desired. And for every person like yourself
who'd see the benefit you're looking for, there are far more that would
find a change in this area a major problem. The concerns around
breakage due to assumed but not required aspects of the relational model
are the ones the users of the software will be confused by, not the
developers of it. You have the classification wrong; the feedback
you've gotten here is from the developers being user oriented, not
theory oriented or code oriented.
--
Greg Smith, 2ndQuadrant US greg@2ndQuadrant.com Baltimore, MD
PostgreSQL Training, Services and Support www.2ndQuadrant.us
Author, "PostgreSQL 9.0 High Performance" Pre-ordering at:
https://www.packtpub.com/postgresql-9-0-high-performance/book
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: Useless sort by
2010-09-13 09:47 Useless sort by Gaetano Mendola <mendola@gmail.com>
2010-09-13 14:44 ` Re: Useless sort by Tom Lane <tgl@sss.pgh.pa.us>
2010-09-13 15:55 ` Re: Useless sort by Gaetano Mendola <mendola@gmail.com>
2010-09-13 16:48 ` Re: Useless sort by Tom Lane <tgl@sss.pgh.pa.us>
2010-09-13 17:09 ` Re: Useless sort by Gaetano Mendola <mendola@gmail.com>
2010-09-23 00:54 ` Re: Useless sort by Robert Haas <robertmhaas@gmail.com>
2010-09-23 03:05 ` Re: Useless sort by gnuoytr@rcn.com
2010-09-23 04:01 ` Re: Useless sort by Greg Smith <greg@2ndquadrant.com>
@ 2010-09-23 04:18 ` Scott Marlowe <scott.marlowe@gmail.com>
0 siblings, 0 replies; 19+ messages in thread
From: Scott Marlowe @ 2010-09-23 04:18 UTC (permalink / raw)
To: Greg Smith <greg@2ndquadrant.com>; +Cc: gnuoytr@rcn.com; pgsql-performance
On Wed, Sep 22, 2010 at 10:01 PM, Greg Smith <greg@2ndquadrant.com> wrote:
> gnuoytr@rcn.com wrote:
>>
>> Spoken like a dyed in the wool COBOL coder. The RM has no need for order;
>> it's set based. I've dabbled in PG for some time, and my sense is
>> increasingly that PG developers are truly code oriented, not database (set)
>> oriented.
>
> I can't tell if you meant for this to be insulting or my reading it that way
> is wrong, but it certainly wasn't put in a helpful tone. Let me summarize
> for you. You've been told that putting ORDER BY into a view is a generally
> poor idea anyway, that it's better to find ways avoid this class of concern
> altogether.
It's been a few years since I've read the SQL spec, but doesn't it
actually forbid order by in views but pgsql allows it anyway?
Like you said, order by in a view is a bad practice to get into, and
it's definitely not what a "set oriented" person would do. it's what
a code oriented person would do.
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: Useless sort by
2010-09-13 09:47 Useless sort by Gaetano Mendola <mendola@gmail.com>
2010-09-13 14:44 ` Re: Useless sort by Tom Lane <tgl@sss.pgh.pa.us>
2010-09-13 15:55 ` Re: Useless sort by Gaetano Mendola <mendola@gmail.com>
2010-09-13 16:48 ` Re: Useless sort by Tom Lane <tgl@sss.pgh.pa.us>
2010-09-13 17:09 ` Re: Useless sort by Gaetano Mendola <mendola@gmail.com>
2010-09-23 00:54 ` Re: Useless sort by Robert Haas <robertmhaas@gmail.com>
2010-09-23 03:05 ` Re: Useless sort by gnuoytr@rcn.com
@ 2010-09-23 04:46 ` Robert Haas <robertmhaas@gmail.com>
2 siblings, 0 replies; 19+ messages in thread
From: Robert Haas @ 2010-09-23 04:46 UTC (permalink / raw)
To: gnuoytr@rcn.com; +Cc: pgsql-performance
On Wed, Sep 22, 2010 at 11:05 PM, <gnuoytr@rcn.com> wrote:
> Spoken like a dyed in the wool COBOL coder. The RM has no need for order; it's set based. I've dabbled in PG for some time, and my sense is increasingly that PG developers are truly code oriented, not database (set) oriented.
I'm struggling to think of an adequate response to this. I think I'm
going to go with: huh?
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: Useless sort by
2010-09-13 09:47 Useless sort by Gaetano Mendola <mendola@gmail.com>
2010-09-13 14:44 ` Re: Useless sort by Tom Lane <tgl@sss.pgh.pa.us>
2010-09-13 15:55 ` Re: Useless sort by Gaetano Mendola <mendola@gmail.com>
2010-09-13 16:48 ` Re: Useless sort by Tom Lane <tgl@sss.pgh.pa.us>
2010-09-13 17:09 ` Re: Useless sort by Gaetano Mendola <mendola@gmail.com>
2010-09-23 00:54 ` Re: Useless sort by Robert Haas <robertmhaas@gmail.com>
2010-09-23 03:05 ` Re: Useless sort by gnuoytr@rcn.com
@ 2010-10-25 10:09 ` Gaetano Mendola <mendola@gmail.com>
2 siblings, 0 replies; 19+ messages in thread
From: Gaetano Mendola @ 2010-10-25 10:09 UTC (permalink / raw)
To: gnuoytr@rcn.com
On 09/23/2010 05:05 AM, gnuoytr@rcn.com wrote:
> Spoken like a dyed in the wool COBOL coder. The RM has no need for order; it's set based. I've dabbled in PG for some time, and my sense is increasingly that PG developers are truly code oriented, not database (set) oriented.
That's a bit harsh. Your sense if fooling you.
Regards
Gaetano Mendola
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: Useless sort by
2010-09-13 09:47 Useless sort by Gaetano Mendola <mendola@gmail.com>
2010-09-13 14:44 ` Re: Useless sort by Tom Lane <tgl@sss.pgh.pa.us>
2010-09-13 15:55 ` Re: Useless sort by Gaetano Mendola <mendola@gmail.com>
2010-09-13 16:48 ` Re: Useless sort by Tom Lane <tgl@sss.pgh.pa.us>
@ 2010-09-14 07:10 ` Heikki Linnakangas <heikki.linnakangas@enterprisedb.com>
2010-09-14 15:09 ` Re: Useless sort by Maciek Sakrejda <msakrejda@truviso.com>
1 sibling, 1 reply; 19+ messages in thread
From: Heikki Linnakangas @ 2010-09-14 07:10 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Gaetano Mendola <mendola@gmail.com>; pgsql-performance
On 13/09/10 19:48, Tom Lane wrote:
> Gaetano Mendola<mendola@gmail.com> writes:
>> Of course I'm not suggesting to take away the "sort by" and give the user
>> an unsorted result, I'm asking why the the optimizer in cases like:
>
>> select unique(a) from v_table_with_order_by;
>
>> doesn't takes away the "order by" inside the view and puts it back "rewriting the
>> query like this:
>
>> select unique(a) from v_table_without_order_by
>> order by a;
>
> That changes the order in which the rows are fed to unique(a). The
> principal real-world use for a non-top-level ORDER BY is exactly to
> determine the order in which rows are fed to a function, so we will
> have a revolt on our hands if we break that.
You could check for volatile functions. I think this could be done
safely. However, it doesn't seem worthwhile, it would be a fair amount
of code, and it's not usually a good idea to put an ORDER BY in a view
or subquery anyway unless you also have volatile functions in there, or
you want to coerce the optimizer to choose a certain plan.
--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: Useless sort by
2010-09-13 09:47 Useless sort by Gaetano Mendola <mendola@gmail.com>
2010-09-13 14:44 ` Re: Useless sort by Tom Lane <tgl@sss.pgh.pa.us>
2010-09-13 15:55 ` Re: Useless sort by Gaetano Mendola <mendola@gmail.com>
2010-09-13 16:48 ` Re: Useless sort by Tom Lane <tgl@sss.pgh.pa.us>
2010-09-14 07:10 ` Re: Useless sort by Heikki Linnakangas <heikki.linnakangas@enterprisedb.com>
@ 2010-09-14 15:09 ` Maciek Sakrejda <msakrejda@truviso.com>
0 siblings, 0 replies; 19+ messages in thread
From: Maciek Sakrejda @ 2010-09-14 15:09 UTC (permalink / raw)
To: Heikki Linnakangas <heikki.linnakangas@enterprisedb.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Gaetano Mendola <mendola@gmail.com>; pgsql-performance
> You could check for volatile functions. I think this could be done safely.
I don't think that's enough. A UDA like last() could have an immutable
sfunc, but still be sensitive to the sort order. I think you'd need
something like a special order-sensitive aggregate definition flag.
---
Maciek Sakrejda | System Architect | Truviso
1065 E. Hillsdale Blvd., Suite 215
Foster City, CA 94404
(650) 242-3500 Main
www.truviso.com
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: Useless sort by
2010-09-13 09:47 Useless sort by Gaetano Mendola <mendola@gmail.com>
@ 2010-09-14 16:15 ` Dave Crooke <dcrooke@gmail.com>
2010-09-14 17:06 ` Re: Useless sort by Gaetano Mendola <mendola@gmail.com>
1 sibling, 1 reply; 19+ messages in thread
From: Dave Crooke @ 2010-09-14 16:15 UTC (permalink / raw)
To: Gaetano Mendola <mendola@gmail.com>; +Cc: pgsql-performance
I presume there is more usage of this view than just those 3 queries
(otherwise, for a start there would be no need for d, e, f in the view
definition)
Why not just rewrite these 3 queries to go directly off the main table? Or,
create a different view without the sort_by in its definition?
Or, if these are used very frequently and performance is critical, consider
(i) caching these results in the application layer, with logic to understand
when they need to be updated, or (b) maintaining extra tables that just
contain (a) (a,b) and (a,b,c)
Objectively, it's always better to optimize the SQL and application level
for the specific needs of the situation before concluding that the
underlying database engine should do these optimizations automatically, and
it seems like there are a number of options you could explore here.
Cheers
Dave
On Mon, Sep 13, 2010 at 4:47 AM, Gaetano Mendola <mendola@gmail.com> wrote:
> Hi all,
> I have a view v_table defined as following:
>
> select a,b,c,d,e,f
> from t_table
> sort by a,b,c;
>
> the usage pattern of this view is the following:
>
> select distinct(a) from v_table;
> select distinct(b) from v_table where a = "XXX";
> select distinct(c) from v_table where a = "XXX" and b = "YYYY";
>
> because of that sort in the view definition the first query above
> takes not less than 3 seconds. I have solved this performance issue
> removing the sort from the view definition and putting it in the
> select reducing the time from > 3secons to < 150ms.
>
> Can not the optimizer take rid of that useless sort on those
> kind of queries ?
>
>
> Regards
> Gaetano Mendola
>
>
>
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: Useless sort by
2010-09-13 09:47 Useless sort by Gaetano Mendola <mendola@gmail.com>
2010-09-14 16:15 ` Re: Useless sort by Dave Crooke <dcrooke@gmail.com>
@ 2010-09-14 17:06 ` Gaetano Mendola <mendola@gmail.com>
0 siblings, 0 replies; 19+ messages in thread
From: Gaetano Mendola @ 2010-09-14 17:06 UTC (permalink / raw)
To: Dave Crooke <dcrooke@gmail.com>; +Cc: pgsql-performance
On Tue, Sep 14, 2010 at 6:15 PM, Dave Crooke <dcrooke@gmail.com> wrote:
> I presume there is more usage of this view than just those 3 queries
> (otherwise, for a start there would be no need for d, e, f in the view
> definition)
>
> Why not just rewrite these 3 queries to go directly off the main table? Or,
> create a different view without the sort_by in its definition?
>
> Or, if these are used very frequently and performance is critical, consider
> (i) caching these results in the application layer, with logic to understand
> when they need to be updated, or (b) maintaining extra tables that just
> contain (a) (a,b) and (a,b,c)
>
> Objectively, it's always better to optimize the SQL and application level
> for the specific needs of the situation before concluding that the
> underlying database engine should do these optimizations automatically, and
> it seems like there are a number of options you could explore here.
Question here is not how to do it right, but how to make the optimizer smarter
than it is now, taking rid of work not needed.
Regards
Gaetano Mendola
--
cpp-today.blogspot.com
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: Useless sort by
@ 2010-09-23 13:51 gnuoytr@rcn.com
2010-09-23 17:11 ` Re: Useless sort by Scott Marlowe <scott.marlowe@gmail.com>
0 siblings, 1 reply; 19+ messages in thread
From: gnuoytr@rcn.com @ 2010-09-23 13:51 UTC (permalink / raw)
To: pgsql-performance
I can't tell if you meant for this to be insulting or my reading it that way is wrong, but it certainly wasn't put in a helpful tone. Let me summarize for you. You've been told that putting ORDER BY into a view is a generally poor idea anyway, that it's better to find ways avoid this class of concern altogether. There are significant non-obvious technical challenges behind actually implementing the behavior you'd like to see; the concerns raised by Tom and Maciek make your idea impractical even if it were desired. And for every person like yourself who'd see the benefit you're looking for, there are far more that would find a change in this area a major problem. The concerns around breakage due to assumed but not required aspects of the relational model are the ones the users of the software will be confused by, not the developers of it. You have the classification wrong; the feedback you've gotten here is from the developers being user oriented, not theory oriented or
c!
ode oriented.
--
Greg Smith, 2ndQuadrant US greg@2ndQuadrant.com Baltimore, MD
PostgreSQL Training, Services and Support www.2ndQuadrant.us
Author, "PostgreSQL 9.0 High Performance" Pre-ordering at:
https://www.packtpub.com/postgresql-9-0-high-performance/book
Not insulting, just amused bemusement. PG portrays itself as the best OS database, which it may well be. But it does so by stressing the row-by-agonizing-row approach to data. In other words, as just a record paradigm filestore for COBOL/java/C coders. I was expecting more Relational oomph. As Dr. Codd says: "A Relational Model of Data for Large Shared Data Banks". Less code, more data.
robert
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: Useless sort by
2010-09-23 13:51 Re: Useless sort by gnuoytr@rcn.com
@ 2010-09-23 17:11 ` Scott Marlowe <scott.marlowe@gmail.com>
0 siblings, 0 replies; 19+ messages in thread
From: Scott Marlowe @ 2010-09-23 17:11 UTC (permalink / raw)
To: gnuoytr@rcn.com; +Cc: pgsql-performance
On Thu, Sep 23, 2010 at 7:51 AM, <gnuoytr@rcn.com> wrote:
> Not insulting, just amused bemusement. PG portrays itself as the best OS database, which it may well be. But it does so by stressing the row-by-agonizing-row approach to data. In other words, as just a record paradigm filestore for COBOL/java/C coders. I was expecting more Relational oomph. As Dr. Codd says: "A Relational Model of Data for Large Shared Data Banks". Less code, more data.
So what, exactly, would give pgsql more relationally "oomph"?
Your assertion feels pretty hand wavy right now.
--
To understand recursion, one must first understand recursion.
^ permalink raw reply [nested|flat] 19+ messages in thread
end of thread, other threads:[~2010-10-25 10:09 UTC | newest]
Thread overview: 19+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-09-13 09:47 Useless sort by Gaetano Mendola <mendola@gmail.com>
2010-09-13 14:44 ` Tom Lane <tgl@sss.pgh.pa.us>
2010-09-13 15:55 ` Gaetano Mendola <mendola@gmail.com>
2010-09-13 16:48 ` Tom Lane <tgl@sss.pgh.pa.us>
2010-09-13 17:09 ` Gaetano Mendola <mendola@gmail.com>
2010-09-13 17:24 ` Maciek Sakrejda <msakrejda@truviso.com>
2010-09-13 19:08 ` Scott Marlowe <scott.marlowe@gmail.com>
2010-09-23 00:54 ` Robert Haas <robertmhaas@gmail.com>
2010-09-23 03:05 ` gnuoytr@rcn.com
2010-09-23 04:01 ` Greg Smith <greg@2ndquadrant.com>
2010-09-23 04:18 ` Scott Marlowe <scott.marlowe@gmail.com>
2010-09-23 04:46 ` Robert Haas <robertmhaas@gmail.com>
2010-10-25 10:09 ` Gaetano Mendola <mendola@gmail.com>
2010-09-14 07:10 ` Heikki Linnakangas <heikki.linnakangas@enterprisedb.com>
2010-09-14 15:09 ` Maciek Sakrejda <msakrejda@truviso.com>
2010-09-14 16:15 ` Dave Crooke <dcrooke@gmail.com>
2010-09-14 17:06 ` Gaetano Mendola <mendola@gmail.com>
2010-09-23 13:51 Re: Useless sort by gnuoytr@rcn.com
2010-09-23 17:11 ` Scott Marlowe <scott.marlowe@gmail.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