From: | Saurabh Nanda <saurabhnanda(at)gmail(dot)com> |
---|---|
To: | Bob Jolliffe <bobjolliffe(at)gmail(dot)com> |
Cc: | pgsql-performance(at)lists(dot)postgresql(dot)org |
Subject: | Re: How can sort performance be so different |
Date: | 2019-01-29 18:36:04 |
Message-ID: | CAPz=2oGFWrSQ7GRk4dt5mS3_79X-AbrDqBkD5W3s=pHOXrvDmg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
Run https://github.com/n-st/nench and benchmark the underlying vps first.
On Tue 29 Jan, 2019, 11:59 PM Bob Jolliffe <bobjolliffe(at)gmail(dot)com wrote:
> The following is output from analyzing a simple query on a table of
> 13436 rows on postgresql 10, ubuntu 18.04.
>
> explain analyze select * from chart order by name;
> QUERY PLAN
>
> -----------------------------------------------------------------------------------------------------------------
> Sort (cost=1470.65..1504.24 rows=13436 width=725) (actual
> time=224340.949..224343.499 rows=13436 loops=1)
> Sort Key: name
> Sort Method: quicksort Memory: 4977kB
> -> Seq Scan on chart (cost=0.00..549.36 rows=13436 width=725)
> (actual time=0.015..1.395 rows=13436 loops=1)
> Planning time: 0.865 ms
> Execution time: 224344.281 ms
> (6 rows)
>
> The planner has predictably done a sequential scan followed by a sort.
> Though it might have wished it hadn't and just used the index (there
> is an index on name). The sort is taking a mind boggling 224 seconds,
> nearly 2 minutes.
>
> This is on a cloud vps server.
>
> Interesting when I run the same query on my laptop it completes in
> well under one second.
>
> I wonder what can cause such a massive discrepancy in the sort time.
> Can it be that the VPS server has heavily over committed CPU. Note I
> have tried this with 2 different company's servers with similar
> results.
>
> I am baffled. The sort seems to be all done in memory (only 5MB).
> Tested when nothing else was going on at the time. I can expect some
> difference between the VPS and my laptop, but almost 1000x seems odd.
> The CPUs are different but not that different.
>
> Any theories?
>
> Regards
> Bob
>
>
From | Date | Subject | |
---|---|---|---|
Next Message | Jerry Sievers | 2019-01-29 18:40:01 | Re: Interpreting shared_buffers setting |
Previous Message | Pavel Stehule | 2019-01-29 18:33:53 | Re: How can sort performance be so different |