pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedSORT performance - slow?
7+ messages / 6 participants
[nested] [flat]
* SORT performance - slow?
@ 2011-05-19 21:13 Strange, John W <john.w.strange@jpmchase.com>
2011-05-19 21:41 ` Re: SORT performance - slow? Samuel Gendler <sgendler@ideasculptor.com>
2011-05-22 23:11 ` Re: SORT performance - slow? Tomas Vondra <tv@fuzzy.cz>
0 siblings, 2 replies; 7+ messages in thread
From: Strange, John W @ 2011-05-19 21:13 UTC (permalink / raw)
To: pgsql-performance
Am I reading this right in that the sort is taking almost 8 seconds?
"GroupAggregate (cost=95808.09..95808.14 rows=1 width=142) (actual time=14186.999..14694.524 rows=315635 loops=1)"
" Output: sq.tag, sq.instrument, s.d1, s.d2, s.d3, s.d4, s.d5, s.d6, s.d7, s.d8, s.d9, s.d10, sum(sq.v)"
" Buffers: shared hit=9763"
" -> Sort (cost=95808.09..95808.09 rows=1 width=142) (actual time=14186.977..14287.068 rows=315635 loops=1)"
" Output: sq.tag, sq.instrument, s.d1, s.d2, s.d3, s.d4, s.d5, s.d6, s.d7, s.d8, s.d9, s.d10, sq.v"
" Sort Key: sq.tag, sq.instrument, s.d1, s.d2, s.d3, s.d4, s.d5, s.d6, s.d7, s.d8, s.d9, s.d10"
" Sort Method: quicksort Memory: 79808kB"
" Buffers: shared hit=9763"
" -> Hash Join (cost=87341.48..95808.08 rows=1 width=142) (actual time=6000.728..12037.492 rows=315635 loops=1)"
" Output: sq.tag, sq.instrument, s.d1, s.d2, s.d3, s.d4, s.d5, s.d6, s.d7, s.d8, s.d9, s.d10, sq.v"
" Hash Cond: (s.scenarioid = sq.scenarioid)"
" Buffers: shared hit=9763"
_______________________________________________________________________________________________
| John W. Strange | Vice President | Global Commodities Technology
| J.P. Morgan | 700 Louisiana, 11th Floor | T: 713-236-4122 | C: 281-744-6476 | F: 713 236-3333
| john.w.strange@jpmchase.com<mailto:john.w.strange@jpmchase.com> | jpmorgan.com
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.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: SORT performance - slow?
2011-05-19 21:13 SORT performance - slow? Strange, John W <john.w.strange@jpmchase.com>
@ 2011-05-19 21:41 ` Samuel Gendler <sgendler@ideasculptor.com>
1 sibling, 0 replies; 7+ messages in thread
From: Samuel Gendler @ 2011-05-19 21:41 UTC (permalink / raw)
To: Strange, John W <john.w.strange@jpmchase.com>; +Cc: pgsql-performance
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.
On Thu, May 19, 2011 at 2:13 PM, Strange, John W <
john.w.strange@jpmchase.com> wrote:
> Am I reading this right in that the sort is taking almost 8 seconds?
>
>
>
> *"GroupAggregate (cost=95808.09..95808.14 rows=1 width=142) (actual
> time=14186.999..14694.524 rows=315635 loops=1)"*
>
> " Output: sq.tag, sq.instrument, s.d1, s.d2, s.d3, s.d4, s.d5, s.d6, s.d7,
> s.d8, s.d9, s.d10, sum(sq.v)"
>
> " Buffers: shared hit=9763"
>
> *" -> Sort (cost=95808.09..95808.09 rows=1 width=142) (actual
> time=14186.977..14287.068 rows=315635 loops=1)"*
>
> " Output: sq.tag, sq.instrument, s.d1, s.d2, s.d3, s.d4, s.d5, s.d6,
> s.d7, s.d8, s.d9, s.d10, sq.v"
>
> " Sort Key: sq.tag, sq.instrument, s.d1, s.d2, s.d3, s.d4, s.d5,
> s.d6, s.d7, s.d8, s.d9, s.d10"
>
> " Sort Method: quicksort Memory: 79808kB"
>
> " Buffers: shared hit=9763"
>
> *" -> Hash Join (cost=87341.48..95808.08 rows=1 width=142)
> (actual time=6000.728..12037.492 rows=315635 loops=1)"*
>
> " Output: sq.tag, sq.instrument, s.d1, s.d2, s.d3, s.d4, s.d5,
> s.d6, s.d7, s.d8, s.d9, s.d10, sq.v"
>
> " Hash Cond: (s.scenarioid = sq.scenarioid)"
>
> " Buffers: shared hit=9763"
>
>
>
>
>
>
> _______________________________________________________________________________________________
> |* John W. Strange* | Vice President | Global Commodities Technology
> | J.P. Morgan | 700 Louisiana, 11th Floor | T: 713-236-4122 | C:
> 281-744-6476 | F: 713 236-3333
> | john.w.strange@jpmchase.com | jpmorgan.com
>
>
>
> 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.
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: SORT performance - slow?
2011-05-19 21:13 SORT performance - slow? Strange, John W <john.w.strange@jpmchase.com>
@ 2011-05-22 23:11 ` Tomas Vondra <tv@fuzzy.cz>
2011-05-23 17:01 ` Re: SORT performance - slow? Maciek Sakrejda <msakrejda@truviso.com>
1 sibling, 1 reply; 7+ messages in thread
From: Tomas Vondra @ 2011-05-22 23:11 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: SORT performance - slow?
2011-05-19 21:13 SORT performance - slow? Strange, John W <john.w.strange@jpmchase.com>
2011-05-22 23:11 ` Re: SORT performance - slow? Tomas Vondra <tv@fuzzy.cz>
@ 2011-05-23 17:01 ` Maciek Sakrejda <msakrejda@truviso.com>
2011-05-23 17:27 ` Re: SORT performance - slow? Robert Haas <robertmhaas@gmail.com>
2011-05-23 17:56 ` Re: SORT performance - slow? Tomas Vondra <tv@fuzzy.cz>
0 siblings, 2 replies; 7+ messages in thread
From: Maciek Sakrejda @ 2011-05-23 17:01 UTC (permalink / raw)
To: Tomas Vondra <tv@fuzzy.cz>; +Cc: pgsql-performance
> 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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: SORT performance - slow?
2011-05-19 21:13 SORT performance - slow? Strange, John W <john.w.strange@jpmchase.com>
2011-05-22 23:11 ` Re: SORT performance - slow? Tomas Vondra <tv@fuzzy.cz>
2011-05-23 17:01 ` Re: SORT performance - slow? Maciek Sakrejda <msakrejda@truviso.com>
@ 2011-05-23 17:27 ` Robert Haas <robertmhaas@gmail.com>
1 sibling, 0 replies; 7+ messages in thread
From: Robert Haas @ 2011-05-23 17:27 UTC (permalink / raw)
To: Maciek Sakrejda <msakrejda@truviso.com>; +Cc: Tomas Vondra <tv@fuzzy.cz>; pgsql-performance
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: SORT performance - slow?
2011-05-19 21:13 SORT performance - slow? Strange, John W <john.w.strange@jpmchase.com>
2011-05-22 23:11 ` Re: SORT performance - slow? Tomas Vondra <tv@fuzzy.cz>
2011-05-23 17:01 ` Re: SORT performance - slow? Maciek Sakrejda <msakrejda@truviso.com>
@ 2011-05-23 17:56 ` Tomas Vondra <tv@fuzzy.cz>
1 sibling, 0 replies; 7+ messages in thread
From: Tomas Vondra @ 2011-05-23 17:56 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: SORT performance - slow?
@ 2011-05-20 03:52 Kevin Grittner <Kevin.Grittner@wicourts.gov>
0 siblings, 0 replies; 7+ messages in thread
From: Kevin Grittner @ 2011-05-20 03:52 UTC (permalink / raw)
To: john.w.strange@jpmchase.com; pgsql-performance
"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
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2011-05-23 17:56 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-05-19 21:13 SORT performance - slow? Strange, John W <john.w.strange@jpmchase.com>
2011-05-19 21:41 ` Samuel Gendler <sgendler@ideasculptor.com>
2011-05-22 23:11 ` Tomas Vondra <tv@fuzzy.cz>
2011-05-23 17:01 ` Maciek Sakrejda <msakrejda@truviso.com>
2011-05-23 17:27 ` Robert Haas <robertmhaas@gmail.com>
2011-05-23 17:56 ` Tomas Vondra <tv@fuzzy.cz>
2011-05-20 03:52 Re: SORT performance - slow? Kevin Grittner <Kevin.Grittner@wicourts.gov>
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