How can sort performance be so different

From: Bob Jolliffe <bobjolliffe(at)gmail(dot)com>
To: pgsql-performance(at)lists(dot)postgresql(dot)org
Subject: How can sort performance be so different
Date: 2019-01-29 18:29:25
Message-ID: CACd=f9feuR3BkZ65Noce_rc2b5R5GYin_9Gd9UaC_rhOBoch5g@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

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

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Jerry Sievers 2019-01-29 18:31:51 Re: Interpreting shared_buffers setting
Previous Message Saurabh Nanda 2019-01-29 18:10:53 Re: Will higher shared_buffers improve tpcb-like benchmarks?