Re: Delay locking partitions during INSERT and UPDATE

On Fri, 18 Jan 2019 at 19:08, sho kato <kato-sho(at)jp(dot)fujitsu(dot)com> wrote:
> I confirmed that this patch improve performance by 10 times or more.

Thanks for testing this out.

> Also, I did make installcheck world, but test partition_prune failed.
> However, this test case failed even before applying a patch, so this patch is not a problem.
> One of the results is as follows.
>
> explain (analyze, costs off, summary off, timing off) execute ab_q1 (2, 2, 3);
> - QUERY PLAN
> ----------------------------------------------------------
> + QUERY PLAN
> +------------------------------------------------------
> Append (actual rows=0 loops=1)
> - Subplans Removed: 6
> -> Seq Scan on ab_a2_b1 (actual rows=0 loops=1)
> - Filter: ((a >= $1) AND (a <= $2) AND (b <= $3))
> + Filter: ((a >= 2) AND (a <= 2) AND (b <= 3))
> -> Seq Scan on ab_a2_b2 (actual rows=0 loops=1)
> - Filter: ((a >= $1) AND (a <= $2) AND (b <= $3))
> + Filter: ((a >= 2) AND (a <= 2) AND (b <= 3))
> -> Seq Scan on ab_a2_b3 (actual rows=0 loops=1)
> - Filter: ((a >= $1) AND (a <= $2) AND (b <= $3))
> -(8 rows)
> + Filter: ((a >= 2) AND (a <= 2) AND (b <= 3))
> +(7 rows)

Perhaps you're running with plan_cache_mode = force_custom_plan.
You'll likely need it set to auto for these to pass.

--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services