From: | Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> |
---|---|
To: | Bob Jolliffe <bobjolliffe(at)gmail(dot)com> |
Cc: | Pgsql Performance <pgsql-performance(at)lists(dot)postgresql(dot)org> |
Subject: | Re: How can sort performance be so different |
Date: | 2019-01-29 18:33:53 |
Message-ID: | CAFj8pRBA_kRbGJBZUh7jnxYcDmzy0an8cmHcZHM6UqxeNeJirw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
ú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
>
>
From | Date | Subject | |
---|---|---|---|
Next Message | Saurabh Nanda | 2019-01-29 18:36:04 | Re: How can sort performance be so different |
Previous Message | Jerry Sievers | 2019-01-29 18:31:51 | Re: Interpreting shared_buffers setting |