Thread: SORT performance - slow?

This communication is for informational purposes only. It is not intended as an offer or solicitation for the purchase or sale of any financial instrument or as an official confirmation of any transaction. All market prices, data and other information are not warranted as to completeness or accuracy and are subject to change without notice. Any comments or statements made herein do not necessarily reflect those of JPMorgan Chase & Co., its subsidiaries and affiliates. This transmission may contain information that is privileged, confidential, legally privileged, and/or exempt from disclosure under applicable law. If you are not the intended recipient, you are hereby notified that any disclosure, copying, distribution, or use of the information contained herein (including any reliance thereon) is STRICTLY PROHIBITED. Although this transmission and any attachments are believed to be free of any virus or other defect that might affect any computer system into which it is received and opened, it is the responsibility of the recipient to ensure that it is virus free and no responsibility is accepted by JPMorgan Chase & Co., its subsidiaries and affiliates, as applicable, for any loss or damage arising in any way from its use. If you received this transmission in error, please immediately contact the sender and destroy the material in its entirety, whether in electronic or hard copy format. Thank you. Please refer to http://www.jpmorgan.com/pages/disclosures for disclosures relating to European legal entities.

Plus the entire explain analyze output into the form at http://explain.depesz.com/ and you'll get a nicely human readable output which shows both the inclusive and exclusive time spent on each step of the query.  It also highlights any steps which show inaccurate statistics.  It will also give you a perma-link which you can use in emails so that everyone else can see the pretty version, too.

"Strange, John W"  wrote:

> Am I reading this right in that the sort is taking almost 8
> seconds?

> -> Sort ... actual time=14186.977..14287.068

>   -> Hash Join ... actual time=6000.728..12037.492

The run time of the sort is the difference between 12037 ms and
14287 ms (the completion times).  That's 2.25 seconds.

> If you are not the intended recipient, you are hereby notified
> that any disclosure, copying, distribution, or use of the
> information contained herein (including any reliance thereon) is
> STRICTLY PROHIBITED.

You probably already know this, but just to make sure -- you posted
this to a public list which is automatically replicated to several
websites freely available to everyone on the planet.

-Kevin

Kevin Grittner wrote:
> "Strange, John W"  wrote:
>> If you are not the intended recipient, you are hereby notified
>> that any disclosure, copying, distribution, or use of the
>> information contained herein (including any reliance thereon) is
>> STRICTLY PROHIBITED.
>
> You probably already know this, but just to make sure -- you posted
> this to a public list which is automatically replicated to several
> websites freely available to everyone on the planet.

It's irrelevant, since that "STRICTLY PROHIBITED" verbiage is irrelevant,
unenforceable and legally meaningless.  I could post that message on my
personal blog, being not the intended recipient myself, and they would b e
utterly powerless to do anything about it even if they sent it privately to my
personal email inbox.

I don't even know why people bother even putting such nonsense into their
emails, let alone Usenet or mailing-list posts.

--
Lew
Honi soit qui mal y pense.
http://upload.wikimedia.org/wikipedia/commons/c/cf/Friz.jpg

Dne 19.5.2011 23:13, Strange, John W napsal(a):
> Am I reading this right in that the sort is taking almost 8 seconds?

You're probably reading it wrong. The sort itself takes about 1 ms (just
subtract the numbers in "actual="). If you include all the overhead it
takes about 2.3 seconds (the hash join ends at 12 sec, the sort at 14.3).

Anyway, your real problem is probably stale stats. Run ANALYZE on the
tables referenced in the query, the estimates are very off. All the rows
expect 1 row but are getting 315k of them.

regards
Tomas

On 05/20/2011 11:47 AM, Lew wrote:

> I don't even know why people bother even putting such nonsense into
> their emails, let alone Usenet or mailing-list posts.

This may sound like a surprise, but many of us don't. Several companies
use an auto-append on any outgoing message not sent to an internal
recipient. You can see this for yourselves in this message, as my
company's little blurb gets attached after my signature lines. It's just
standard boilerplate meant as a CYA measure, really.

--
Shaun Thomas
OptionsHouse | 141 W. Jackson Blvd. | Suite 800 | Chicago IL, 60604
312-676-8870
sthomas@peak6.com

______________________________________________

See  http://www.peak6.com/email_disclaimer.php
for terms and conditions related to this email

> You're probably reading it wrong. The sort itself takes about 1 ms (just
> subtract the numbers in "actual=").

I thought it was cost=startup_cost..total_cost. That is not quite the
same thing, since startup_cost is effectively "cost to produce first
row", and Sort can't really operate in a "streaming" fashion (well,
theoretically, something like selection sort could, but that's beside
the point) so it needs to do all the work up front. I'm no explain
expert, so someone please correct me if I'm wrong.

---
Maciek Sakrejda | System Architect | Truviso

1065 E. Hillsdale Blvd., Suite 215
Foster City, CA 94404
(650) 242-3500 Main
www.truviso.com

On Mon, May 23, 2011 at 1:01 PM, Maciek Sakrejda <msakrejda@truviso.com> wrote:
>> You're probably reading it wrong. The sort itself takes about 1 ms (just
>> subtract the numbers in "actual=").
>
> I thought it was cost=startup_cost..total_cost. That is not quite the
> same thing, since startup_cost is effectively "cost to produce first
> row", and Sort can't really operate in a "streaming" fashion (well,
> theoretically, something like selection sort could, but that's beside
> the point) so it needs to do all the work up front. I'm no explain
> expert, so someone please correct me if I'm wrong.

You are right.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Dne 23.5.2011 19:01, Maciek Sakrejda napsal(a):
>> You're probably reading it wrong. The sort itself takes about 1 ms (just
>> subtract the numbers in "actual=").
>
> I thought it was cost=startup_cost..total_cost. That is not quite the
> same thing, since startup_cost is effectively "cost to produce first
> row", and Sort can't really operate in a "streaming" fashion (well,
> theoretically, something like selection sort could, but that's beside
> the point) so it needs to do all the work up front. I'm no explain
> expert, so someone please correct me if I'm wrong.

Good point, thanks. In that case the second number (2.3 sec) is correct.

I still think the problem is not the sorting but the inaccurate
estimates - fixing this might yield a much better / faster plan. But the
OP posted just a small part of the plan, so it's hard to guess.

regards
Tomas