Re: How can sort performance be so different

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Run https://github.com/n-st/nench and benchmark the underlying vps first. 

On Tue 29 Jan, 2019, 11:59 PM Bob Jolliffe <bobjolliffe@xxxxxxxxx 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


[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux