Performance issue with order by clause on

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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 typecountsum of times% of query
Hash18.844 ms10.0 %
Hash Left Join133.715 ms38.0 %
Insert10.734 ms0.8 %
Limit10.003 ms0.0 %
Seq Scan222.735 ms25.6 %
Sort122.571 ms25.5 %
Subquery Scan10.046 ms0.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:

image.png


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

[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux