Re: Delay locking partitions during query execution - Mailing list pgsql-hackers
From | Tomas Vondra |
---|---|
Subject | Re: Delay locking partitions during query execution |
Date | |
Msg-id | c242748a-d448-fea3-aad3-a80538051435@2ndquadrant.com Whole thread Raw |
In response to | Re: Delay locking partitions during query execution (David Rowley <david.rowley@2ndquadrant.com>) |
Responses |
Re: Delay locking partitions during query execution
|
List | pgsql-hackers |
On 1/4/19 1:53 AM, David Rowley wrote: > On Fri, 4 Jan 2019 at 13:01, Tomas Vondra <tomas.vondra@2ndquadrant.com> wrote: >> On 1/3/19 11:57 PM, David Rowley wrote: >>> You'll know you're getting a generic plan when you see "Filter (a = >>> $1)" and see "Subplans Removed: 9999" below the Append. >>> >> >> Indeed, with prepared statements I now see some improvements: >> >> partitions 0 100 1000 10000 >> -------------------------------------------- >> master 19 1590 2090 128 >> patched 18 1780 6820 1130 >> >> So, that's nice. I wonder why the throughput drops so fast between 1k >> and 10k partitions, but I'll look into that later. > > Those look strange. Why is it so slow with the non-partitioned case? > I'd have expected that to be the fastest result. > Because there are 1M rows in the table, and it's doing a seqscan. That also makes the other cases difficult to compare, because with few partitions there will be multiple pages per partition, scanned sequentially. And with many partitions it's likely only a single page with a couple of rows on it. I'll think about constructing a better benchmark, to make it easier to compare - perhaps by using a single row per table and/or adding indexes. Or something ... >> Does this mean this optimization can only ever work with prepared >> statements, or can it be made to work with regular plans too? > > That's a good question. I confirm it's only any use of run-time > pruning occurs during init plan. For this patch to be any use, you'll > need to see a "Subplans Removed: <N>" in the query's EXPLAIN ANALYZE > output. If you don't see this then all Append/MergeAppend subplans > were initialised and the relation lock would have been obtained > regardless of if delaylock is set for the relation. The effect of the > patch here would just have been to obtain the lock during the first > call to ExecGetRangeTableRelation() for that relation instead of > during AcquireExecutorLocks(). There may actually be a tiny overhead > in this case since AcquireExecutorLocks() must skip the delaylock > relations, but they'll get locked later anyway. I doubt you could > measure that though. > > When run-time pruning is able to prune partitions before execution > starts then the optimisation is useful since AcquireExecutorLocks() > won't obtain the lock and ExecGetRangeTableRelation() won't be called > for all pruned partition's rels as we don't bother to init the > Append/MergeAppend subplan for those. > > I'm a little unsure if there are any cases where this type of run-time > pruning can occur when PREPAREd statements are not in use. Initplan > parameters can't prune before executor run since we need to run the > executor to obtain the values of those. Likewise for evaluation of > volatile functions. So I think run-time pruning before initplan is > only ever going to happen for PARAM_EXTERN type parameters, i.e. with > PREPAREd statements (REF: analyze_partkey_exprs() partprune.c). > Without PREPAREd statements, if the planner itself was unable to prune > the partitions it would already have obtained the lock during > planning, so AcquireExecutorLocks(), in this case, would bump into the > local lock hash table entry and forego trying to obtain the lock > itself. That's not free, but it's significantly faster than obtaining > a lock. > > Or in short... it only good for prepared statements where the > statement's parameters allow for run-time pruning. However, that's a > pretty large case since the planner is still very slow at planning for > large numbers of partitions, meaning it's common (or at least it will > be) for people to use PREPAREd statement and plan_cache_mode = > force_generic_plan; > OK, thanks for the explanation. One more reason to use prepared statements in such cases ... regards -- Tomas Vondra http://www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
pgsql-hackers by date: