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.