Re: speeding up planning with partitions

From: Amit Langote <Langote_Amit_f8(at)lab(dot)ntt(dot)co(dot)jp>
To: "Imai, Yoshikazu" <imai(dot)yoshikazu(at)jp(dot)fujitsu(dot)com>
Cc: David Rowley <david(dot)rowley(at)2ndquadrant(dot)com>, Alvaro Herrera <alvherre(at)2ndquadrant(dot)com>, Pg Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: speeding up planning with partitions
Date: 2019-01-17 10:24:47
Message-ID: 1cb56f0f-43f6-5d36-35c7-9d5e7199eeed@lab.ntt.co.jp
Views: Raw Message | Whole Thread | Download mbox
Thread:
Lists: pgsql-hackers

Thank you Imai-san for testing. Sorry it totally slipped my mind to reply
to this email.

On 2019/01/09 11:08, Imai, Yoshikazu wrote:
> I wonder why force_custom_plan is faster than auto after applied the patch.
>
> When we use PREPARE-EXECUTE, a generic plan is created and used if its cost is
> cheaper than creating and using a custom plan with plan_cache_mode='auto',
> while a custom plan is always created and used with plan_cache_mode='force_custom_plan'.
> So one can think the difference in above results is because of creating or
> using a generic plan.
>
> I checked how many times a generic plan is created during executing pgbench and
> found a generic plan is created only once and custom plans are created at other
> times with plan_cache_mode='auto'. I also checked the time of creating a
> generic plan, but it didn't take so much(250ms or so with 4096 partitions). So
> the time of creating a generic plan does not affect the performance.
>
> Currently, a generic plan is created at sixth time of executing EXECUTE query.
> I changed it to more later (ex. at 400,000th time of executing EXECUTE query on
> master with 4096 partitions, because 7000TPS x 60sec=420,0000 transactions are
> run while executing pgbench.), then there are almost no difference between auto
> and force_custom_plan. I think that creation of a generic plan affects the time
> of executing queries which are ordered after creating generic plan.
>
> If my assumption is right, we can expect some additional process is occurred at
> executing queries ordered after creating a generic plan, which results in auto is
> slower than force_custom_plan because of additional process. But looking at
> above results, on master with 4096 partitions, auto is faster than force_custom_plan.
> So now I am confused.
>
> Do you have any ideas what does affect the performance?

Are you saying that, when using auto mode, all executions of the query
starting from 7th are slower than the first 5 executions? That is, the
latency of creating and using a custom plan increases *after* a generic
plan is created and discarded on the 6th execution of the query? If so,
that is inexplicable to me.

Thanks,
Amit

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Adrien NAYRAT 2019-01-17 10:30:01 Re: Log a sample of transactions
Previous Message Chris Travers 2019-01-17 09:50:56 Re: Proposal for Signal Detection Refactoring