Hi, Debug symbols can only be enabled during configure? How about when Postgresql is running?
Regards, Richard Lee On Fri, Nov 2, 2018 at 9:55 PM Tomas Vondra <tomas.von...@2ndquadrant.com> wrote: > On 11/02/2018 10:36 AM, Richard Lee wrote: > > 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. > > > > Formatted explain: https://explain.depesz.com/s/R834 > > > > 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 <http://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 > > > > Those changes likely affect the query costing and execution, but the > number of plans to consider is probably not going to change much. So > planning taking about the same time is kinda expected here. > > > What are my other options to improve the query planning time? > > > > Can you do a bit of profiling, to determine which part of the query > planning process is slow here? That is: > > 1) make sure you have the debug symbols installed > 2) do `perf record` > 3) run the benchmark for a while (a minute or so) > 4) stop the perf record using Ctrl-C > 5) generate a profile using `perf report` and share the result > > Possibly do the same thing with `perf record -g` to collect call-graph > information, but that's probably going way larger. > > regards > > -- > Tomas Vondra http://www.2ndQuadrant.com > PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services >
