Fwd: Query with high planning time compared to execution time

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

 



Hi,

I'm running a performance test for our application and encountered a particular query with high planning time compared to the execution. Please refer to attached explain.out for the explain analyze output.


The test was performed with Jmeter sending requests to the database, query was generated by Hibernate which consists of a 133 table UNION. Also attached are some diagnostic info (database version, database settings, table definitions and maintenance related information).

Due to the extremely large query text, I'm choosing to provide information via attachments instead of pasting in the email body.

Below are some additional OS information on the database server:
CPU: 8
RAM: 24GB
Disk: SSD
OS: CentOS Linux release 7.4.1708 (Core)

[root@kvrh7os202 ~]# uname -a
Linux kvrh7os202.comptel.com 3.10.0-693.21.1.el7.x86_64 #1 SMP Wed Mar 7 19:03:37 UTC 2018 x86_64 x86_64 x86_64 GNU/Linux
[root@kvrh7os202 ~]#

Things I tried:
1. Setting random_page_cost = 1.1 and effective_io_concurrency = 200 - no effect on planning time
2. Create materialized view for big UNION query - planning time reduced significantly but not a viable solution

What are my other options to improve the query planning time?



Regards,
Richard Lee

Attachment: explain.out
Description: Binary data

<<attachment: diag_info.zip>>


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

  Powered by Linux