sort performance better with little memory than big memory

From: yang zhao <azurezhao8369(at)gmail(dot)com>
To: pgsql-general(at)lists(dot)postgresql(dot)org, pgsql-performance(at)lists(dot)postgresql(dot)org
Subject: sort performance better with little memory than big memory
Date: 2022-12-02 04:40:21
Message-ID: CAHQyAqLtwUCz3zqYne=V2efdLk7wR4TGbqeZXBu_JMc14taxzg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

hello,
I am running some sorting sql on my machine,test data is tpch100g, and sql
is:explain analyze verbose select l_shipdate,l_orderkey from lineitem_0
order by l_shipdate,l_orderkey desc .
I found that when I set work_mem to 65MB,sort method is external merge with
disk,which cost 50s in my server.
and when I set work_mem to 6GB,sort method is quicksort in memory, which
cost 78s in same server.
It is strange that more memory bring worse performance.I used perf and find
that when work_mem is 6GB,L1-dcache-load-misses is much more than 64MB when
qsort and tuplesort_gettuple_common.
So,can we try to split memory to pieces and qsort every one,and merge than
all in memory,I have tried this in my local code, and got about 12%
improvement when memory is enough.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Martijn Tonies (Upscene Productions) 2022-12-02 07:49:32 Re: Stored procedure code no longer stored in v14 and v15, changed behaviour
Previous Message Zheng Li 2022-12-02 02:46:25 Re: Support logical replication of DDLs