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
