Re: How can sort performance be so different

út 29. 1. 2019 v 19:29 odesílatel Bob Jolliffe <bobjolliffe(at)gmail(dot)com>
napsal:

> 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?
>

I am sure so sort of 10K rows cannot be 224sec. Really looks like VPS issue.

Regards

Pavel

> Regards
> Bob
>
>