Subject: Re: Fwd: Query with high planning time compared to execution time
To: Richard Lee <swift1984@gmail.com>, pgsql-performance@lists.postgresql.org
References: 
 <CALhvPTQJ12d1VEOoekJNt6+zDuSfcubqP3WO47OiUXUVBR3o3g@mail.gmail.com>
 <CALhvPTS3XmDa7Px2WdZhyvwHPsqC=KKQ2kTsGL0KZiuwGF8C4g@mail.gmail.com>
From: Tomas Vondra <tomas.vondra@2ndquadrant.com>
Message-ID: <8db7abc2-9ddf-858c-e3db-af014cf2ccd9@2ndquadrant.com>
Date: Fri, 2 Nov 2018 14:55:17 +0100
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:52.0) Gecko/20100101
 Thunderbird/52.5.2
MIME-Version: 1.0
In-Reply-To: 
 <CALhvPTS3XmDa7Px2WdZhyvwHPsqC=KKQ2kTsGL0KZiuwGF8C4g@mail.gmail.com>
Content-Type: text/plain; charset=utf-8
Content-Language: en-US
Content-Transfer-Encoding: 8bit
Precedence: bulk

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