Re: Performance issue with order by clause on - Mailing list pgsql-performance

Hi,

I would like to overcome an issue which occurs only in case with order by clause.

Details:

I am trying to insert into a temporary table 50 rows from a joined table ordered by a modification time column which is inserted by the current time so it is ordered ascending.

Each table has index on the following columns: PRIMARY KEY(SystemID, ObjectID, ElementID,  ModificationTime)

Statement:

sqlString := 'INSERT INTO ResultTable (

SELECT * FROM "TABLE" a  LEFT OUTER JOIN "TABLE_Text" l1031  ON  a.ModificationTime = l1031.ModificationTime AND a.SystemID = l1031.SystemID AND a.ObjectID = l1031.ObjectID AND a.ElementID = l1031.ElementID  AND l1031.LCID = 1031 LEFT OUTER JOIN  ( SELECT * AS CommentNumber FROM "TABLE_Comment" v1  GROUP BY v1.ModificationTime, v1.SystemID, v1.ObjectID, v1.ElementID ) c ON  a.ModificationTime = c.ModificationTime AND a.SystemID = c.SystemID AND a.ObjectID = c.ObjectID AND a.ElementID = c.ElementID WHERE a.ModificationTime BETWEEN $1 AND $2  AND ( a.Enabled = 1 ) ORDER BY a.ModificationTime DESC LIMIT 50));

EXECUTE sqlString USING StartTime,EndTime;  

node type	count	sum of times	% of query
Hash	1	8.844 ms	10.0 %
Hash Left Join	1	33.715 ms	38.0 %
Insert	1	0.734 ms	0.8 %
Limit	1	0.003 ms	0.0 %
Seq Scan	2	22.735 ms	25.6 %
Sort	1	22.571 ms	25.5 %
Subquery Scan	1	0.046 ms	0.1 %

Execution Plan: https://explain.depesz.com/s/S96g (Obfuscated)

If I remove the order by clause I get the following results:

node type	count	sum of times	% of query
Index Scan	2	27.632 ms	94.9 %
Insert	1	0.848 ms	2.9 %
Limit	1	0.023 ms	0.1 %
Merge Left Join	1	0.423 ms	1.5 %
Result	1	0.000 ms	0.0 %
Subquery Scan	1	0.186 ms	0.6 %

Which is pointing me to a problem with the sorting. Is there any way that I could improve the performance with order by clause?

To make the problem more transparent I ran a long run test where you can see that with order by clause the performance is linearly getting worse:

Postgresql version: "PostgreSQL 11.1, compiled by Visual C++ build 1914, 64-bit"

Istalled by: With  EnterpriseDB One-click installer from EDB's offical site.

Postgresql.conf changes: Used pgtune suggestions:

# DB Version: 11 

# OS Type: windows 

# DB Type: desktop 

# Total Memory (RAM): 8 GB 

# CPUs num: 4 

# Connections num: 25 

# Data Storage: hdd 

max_connections = 25 

shared_buffers = 512MB 

effective_cache_size = 2GB 

maintenance_work_mem = 512MB 

checkpoint_completion_target = 0.5 

wal_buffers = 16MB 

default_statistics_target = 100 

random_page_cost = 4 

work_mem = 8738kB 

min_wal_size = 100MB 

max_wal_size = 1GB 

max_worker_processes = 4 

max_parallel_workers_per_gather = 2 

max_parallel_workers = 4

Operating System: Windows 10 x64, Version: 1607

Thanks in advance,

Best Regards,

Tom Nay