Re: Skip partition tuple routing with constant partition key

On Tue, Aug 2, 2022 at 6:58 AM David Rowley wrote: > On Thu, 28 Jul 2022 at 19:37, Amit Langote wrote: > > > > On Thu, Jul 28, 2022 at 11:59 AM David Rowley wrote: > > > > I'd quite like to push this patch early next week, so if anyone else > > > is following along that might have any

Re: Skip partition tuple routing with constant partition key

On Thu, 28 Jul 2022 at 19:37, Amit Langote wrote: > > On Thu, Jul 28, 2022 at 11:59 AM David Rowley wrote: > > I'd quite like to push this patch early next week, so if anyone else > > is following along that might have any objections, could they do so > > before then? > > I have no more

RE: Skip partition tuple routing with constant partition key

On Thursday, July 28, 2022 10:59 AM David Rowley wrote: > On Thu, 28 Jul 2022 at 00:50, Amit Langote > wrote: > > So, in a way the caching scheme works for LIST partitioning only if > > the same value appears consecutively in the input set, whereas it does > > not for *a set of* values belonging

Re: Skip partition tuple routing with constant partition key

On Thu, Jul 28, 2022 at 11:59 AM David Rowley wrote: > On Thu, 28 Jul 2022 at 00:50, Amit Langote wrote: > > So, in a way the caching scheme works for > > LIST partitioning only if the same value appears consecutively in the > > input set, whereas it does not for *a set of* values belonging to

Re: Skip partition tuple routing with constant partition key

On Thu, 28 Jul 2022 at 00:50, Amit Langote wrote: > So, in a way the caching scheme works for > LIST partitioning only if the same value appears consecutively in the > input set, whereas it does not for *a set of* values belonging to the > same partition appearing consecutively. Maybe that's a

Re: Skip partition tuple routing with constant partition key

On Wed, Jul 27, 2022 at 7:28 AM David Rowley wrote: > On Sat, 23 Jul 2022 at 01:23, Amit Langote wrote: > > + /* > > +* The Datum has changed. Zero the number of times > > we've > > +* found last_found_datum_index in a row. > > +

Re: Skip partition tuple routing with constant partition key

On Tue, Jul 26, 2022 at 3:28 PM David Rowley wrote: > Thank for looking at this. > > On Sat, 23 Jul 2022 at 01:23, Amit Langote > wrote: > > + /* > > +* The Datum has changed. Zero the number of times > we've > > +* found

Re: Skip partition tuple routing with constant partition key

Thank for looking at this. On Sat, 23 Jul 2022 at 01:23, Amit Langote wrote: > + /* > +* The Datum has changed. Zero the number of times we've > +* found last_found_datum_index in a row. > +*/ > +

Re: Skip partition tuple routing with constant partition key

On Thu, Jul 14, 2022 at 2:31 PM David Rowley wrote: > I've spent some time looking at the v10 patch, and to be honest, I > don't really like the look of it :( Thanks for the review and sorry for the delay in replying. > 1. I think we should be putting the cache fields in PartitionDescData >

Re: Skip partition tuple routing with constant partition key

On Wed, Apr 13, 2022 at 2:39 PM Amit Kapila wrote: > > On Tue, Apr 12, 2022 at 6:16 AM Masahiko Sawada wrote: > > > > Hi, > > > > On Thu, Apr 7, 2022 at 4:37 PM Andres Freund wrote: > > > > > > Hi, > > > > > > On 2022-04-06 00:07:07 -0400, Tom Lane wrote: > > > > Amit Langote writes: > > > > >

Re: Skip partition tuple routing with constant partition key

On Tue, Apr 12, 2022 at 6:16 AM Masahiko Sawada wrote: > > Hi, > > On Thu, Apr 7, 2022 at 4:37 PM Andres Freund wrote: > > > > Hi, > > > > On 2022-04-06 00:07:07 -0400, Tom Lane wrote: > > > Amit Langote writes: > > > > On Sun, Apr 3, 2022 at 10:31 PM Greg Stark wrote: > > > >> Is this a

Re: Skip partition tuple routing with constant partition key

Hi, On Thu, Apr 7, 2022 at 4:37 PM Andres Freund wrote: > > Hi, > > On 2022-04-06 00:07:07 -0400, Tom Lane wrote: > > Amit Langote writes: > > > On Sun, Apr 3, 2022 at 10:31 PM Greg Stark wrote: > > >> Is this a problem with the patch or its tests? > > >> [18:14:20.798] Test Summary Report > >

Re: Skip partition tuple routing with constant partition key

Hi, On 2022-04-06 00:07:07 -0400, Tom Lane wrote: > Amit Langote writes: > > On Sun, Apr 3, 2022 at 10:31 PM Greg Stark wrote: > >> Is this a problem with the patch or its tests? > >> [18:14:20.798] Test Summary Report > >> [18:14:20.798] --- > >> [18:14:20.798]

Re: Skip partition tuple routing with constant partition key

Amit Langote writes: > On Sun, Apr 3, 2022 at 10:31 PM Greg Stark wrote: >> Is this a problem with the patch or its tests? >> [18:14:20.798] Test Summary Report >> [18:14:20.798] --- >> [18:14:20.798] t/013_partition.pl (Wstat: 15360 Tests: 31 Failed: 0) > Hmm, make check-world

Re: Skip partition tuple routing with constant partition key

On Sun, Apr 3, 2022 at 10:31 PM Greg Stark wrote: > Is this a problem with the patch or its tests? > > [18:14:20.798] # poll_query_until timed out executing this query: > [18:14:20.798] # SELECT count(1) = 0 FROM pg_subscription_rel WHERE > srsubstate NOT IN ('r', 's'); > [18:14:20.798] #

Re: Skip partition tuple routing with constant partition key

Is this a problem with the patch or its tests? [18:14:20.798] # poll_query_until timed out executing this query: [18:14:20.798] # SELECT count(1) = 0 FROM pg_subscription_rel WHERE srsubstate NOT IN ('r', 's'); [18:14:20.798] # expecting this output: [18:14:20.798] # t [18:14:20.798] # last

Re: Skip partition tuple routing with constant partition key

On Thu, Mar 24, 2022 at 1:55 AM Zhihong Yu wrote: > On Wed, Mar 23, 2022 at 5:52 AM Amit Langote wrote: >> I've attached an updated version of the patch, though I haven't >> changed the threshold constant. > + * Threshold of the number of tuples to need to have been processed before > + *

Re: Skip partition tuple routing with constant partition key

On Wed, Mar 23, 2022 at 5:52 AM Amit Langote wrote: > Hi Greg, > > On Wed, Mar 16, 2022 at 6:54 AM Greg Stark wrote: > > There are a whole lot of different patches in this thread. > > > > However this last one https://commitfest.postgresql.org/37/3270/ > > created by Amit seems like a fairly

Re: Skip partition tuple routing with constant partition key

Hi Greg, On Wed, Mar 16, 2022 at 6:54 AM Greg Stark wrote: > There are a whole lot of different patches in this thread. > > However this last one https://commitfest.postgresql.org/37/3270/ > created by Amit seems like a fairly straightforward optimization that > can be evaluated on its own

Re: Skip partition tuple routing with constant partition key

There are a whole lot of different patches in this thread. However this last one https://commitfest.postgresql.org/37/3270/ created by Amit seems like a fairly straightforward optimization that can be evaluated on its own separately from the others and seems quite mature. I'm actually inclined to

Re: Skip partition tuple routing with constant partition key

I noticed that there is no CF entry for this, so created one in the next CF: https://commitfest.postgresql.org/34/3270/ Rebased patch attached. v8-0001-Teach-get_partition_for_tuple-to-cache-bound-offs.patch Description: Binary data

Re: Skip partition tuple routing with constant partition key

On Thu, Jun 17, 2021 at 4:18 PM Zhihong Yu wrote: > On Wed, Jun 16, 2021 at 10:37 PM Amit Langote wrote: >> > + if (part_index < 0) >> > + { >> > + bound_offset = >> > partition_range_datum_bsearch(key->partsupfunc, >> > >> > Do we need

Re: Skip partition tuple routing with constant partition key

On Wed, Jun 16, 2021 at 10:37 PM Amit Langote wrote: > Hi, > > Thanks for reading the patch. > > On Thu, Jun 17, 2021 at 1:46 PM Zhihong Yu wrote: > > On Wed, Jun 16, 2021 at 9:29 PM Amit Langote > wrote: > >> Attached a slightly revised version of that patch, with a commit > >> message this

Re: Skip partition tuple routing with constant partition key

Hi, Thanks for reading the patch. On Thu, Jun 17, 2021 at 1:46 PM Zhihong Yu wrote: > On Wed, Jun 16, 2021 at 9:29 PM Amit Langote wrote: >> Attached a slightly revised version of that patch, with a commit >> message this time. > > + int n_tups_inserted; > + int

Re: Skip partition tuple routing with constant partition key

On Wed, Jun 16, 2021 at 9:29 PM Amit Langote wrote: > On Wed, Jun 16, 2021 at 4:27 PM Amit Langote > wrote: > > On Mon, Jun 7, 2021 at 8:38 PM houzj.f...@fujitsu.com > > wrote: > > > The rough idea is to check the average batch number every 1000 rows. > > > If the average batch num is greater

Re: Skip partition tuple routing with constant partition key

On Wed, Jun 16, 2021 at 4:27 PM Amit Langote wrote: > On Mon, Jun 7, 2021 at 8:38 PM houzj.f...@fujitsu.com > wrote: > > The rough idea is to check the average batch number every 1000 rows. > > If the average batch num is greater than 1, then we enable the cache check, > > if not, disable cache

Re: Skip partition tuple routing with constant partition key

Hou-san, On Mon, Jun 7, 2021 at 8:38 PM houzj.f...@fujitsu.com wrote: > Thanks for the patch and explanation, I think you are right that it’s better > add > the rechecking-cached-offset code directly in get_partiiton_for_tuple(). > > And now, I think maybe it's time to try to optimize the

RE: Skip partition tuple routing with constant partition key

Hi Amit-san From: Amit Langote > On Fri, Jun 4, 2021 at 6:05 PM Amit Langote > wrote: > > On Fri, Jun 4, 2021 at 4:38 PM Amit Langote > wrote: > > > On Thu, Jun 3, 2021 at 8:48 PM Amit Langote > wrote: > > > > On Tue, Jun 1, 2021 at 5:43 PM houzj.f...@fujitsu.com > > > > wrote: > > > > > So,

Re: Skip partition tuple routing with constant partition key

On Fri, Jun 4, 2021 at 6:05 PM Amit Langote wrote: > On Fri, Jun 4, 2021 at 4:38 PM Amit Langote wrote: > > On Thu, Jun 3, 2021 at 8:48 PM Amit Langote wrote: > > > On Tue, Jun 1, 2021 at 5:43 PM houzj.f...@fujitsu.com > > > wrote: > > > > So, If we want to share the cached partition between

Re: Skip partition tuple routing with constant partition key

On Fri, Jun 4, 2021 at 4:38 PM Amit Langote wrote: > On Thu, Jun 3, 2021 at 8:48 PM Amit Langote wrote: > > On Tue, Jun 1, 2021 at 5:43 PM houzj.f...@fujitsu.com > > wrote: > > > So, If we want to share the cached partition between statements, we seems > > > cannot > > > use

Re: Skip partition tuple routing with constant partition key

On Thu, Jun 3, 2021 at 8:48 PM Amit Langote wrote: > On Tue, Jun 1, 2021 at 5:43 PM houzj.f...@fujitsu.com > wrote: > > So, If we want to share the cached partition between statements, we seems > > cannot > > use ExecPartitionCheck. Instead, I tried directly invoke the partition > > support >

Re: Skip partition tuple routing with constant partition key

Hou-san, On Tue, Jun 1, 2021 at 5:43 PM houzj.f...@fujitsu.com wrote: > From: Amit Langote > > > > Where are you thinking to cache the partidx list? Inside > > > > PartitionDesc or some executor struct? > > > > > > I was thinking cache the partidx list in PartitionDescData which is in > > >

RE: Skip partition tuple routing with constant partition key

Hi Amit-san From: Amit Langote Sent: Thursday, May 27, 2021 4:46 PM > Hou-san, > > On Thu, May 27, 2021 at 3:56 PM houzj.f...@fujitsu.com > wrote: > > From: Amit Langote > > Sent: Thursday, May 27, 2021 1:54 PM > > > On Thu, May 27, 2021 at 11:47 AM houzj.f...@fujitsu.com > > > wrote: > > >

Re: Skip partition tuple routing with constant partition key

Hou-san, On Thu, May 27, 2021 at 3:56 PM houzj.f...@fujitsu.com wrote: > From: Amit Langote > Sent: Thursday, May 27, 2021 1:54 PM > > On Thu, May 27, 2021 at 11:47 AM houzj.f...@fujitsu.com > > wrote: > > > About teaching relcache about caching the target partition. > > > > > > David-san

RE: Skip partition tuple routing with constant partition key

From: Amit Langote Sent: Thursday, May 27, 2021 1:54 PM > On Thu, May 27, 2021 at 11:47 AM houzj.f...@fujitsu.com > wrote: > > About teaching relcache about caching the target partition. > > > > David-san suggested cache the partidx in PartitionDesc. > > And it will need looping and checking the

Re: Skip partition tuple routing with constant partition key

On Thu, May 27, 2021 at 11:47 AM houzj.f...@fujitsu.com wrote: > About teaching relcache about caching the target partition. > > David-san suggested cache the partidx in PartitionDesc. > And it will need looping and checking the cached value at each level. > I was thinking can we cache a partidx

Re: Skip partition tuple routing with constant partition key

On Thu, May 27, 2021 at 1:55 PM Zhihong Yu wrote: > For CanUseSavedPartitionForTuple, nit: you can check > !dispatch->savedPartResultInfo at the beginning and return early. > This would save some indentation. Sure, see the attached. -- Amit Langote EDB: http://www.enterprisedb.com

Re: Skip partition tuple routing with constant partition key

On Wed, May 26, 2021 at 9:22 PM Amit Langote wrote: > Hi, > > On Thu, May 27, 2021 at 2:30 AM Zhihong Yu wrote: > >> > >> Hi, Amit: > > > > > > For ConvertTupleToPartition() in > 0001-ExecFindPartition-cache-last-used-partition-v3.patch: > > > > + if (tempslot != NULL) > > +

Re: Skip partition tuple routing with constant partition key

Hi, On Thu, May 27, 2021 at 2:30 AM Zhihong Yu wrote: >> >> Hi, Amit: > > > For ConvertTupleToPartition() in > 0001-ExecFindPartition-cache-last-used-partition-v3.patch: > > + if (tempslot != NULL) > + ExecClearTuple(tempslot); > > If tempslot and parent_slot point to the same

RE: Skip partition tuple routing with constant partition key

Hi amit-san From: Amit Langote Sent: Wednesday, May 26, 2021 9:38 AM > > Hou-san, > > On Wed, May 26, 2021 at 10:05 AM houzj.f...@fujitsu.com > wrote: > > > > Thanks for the explanation ! > > Yeah, we can get all the parent table info from PartitionTupleRouting when > INSERT into a

Re: Skip partition tuple routing with constant partition key

> > Hi, Amit: > For ConvertTupleToPartition() in 0001-ExecFindPartition-cache-last-used-partition-v3.patch: + if (tempslot != NULL) + ExecClearTuple(tempslot); If tempslot and parent_slot point to the same slot, should ExecClearTuple() still be called ? Cheers

Re: Skip partition tuple routing with constant partition key

Hou-san, On Wed, May 26, 2021 at 10:05 AM houzj.f...@fujitsu.com wrote: > From: Amit Langote > Sent: Tuesday, May 25, 2021 10:06 PM > > Though again, I think we can do this without changing the relcache > > interface, > > such as RelationGetPartitionQual(). > > > > PartitionTupleRouting has

RE: Skip partition tuple routing with constant partition key

Hi Amit-san From: Amit Langote Sent: Tuesday, May 25, 2021 10:06 PM > Hou-san, > > Thanks for the patch and It looks more compact than mine. > > > > After taking a quick look at the patch, I found a possible issue. > > Currently, the patch does not search the parent's partition key expression >

Re: Skip partition tuple routing with constant partition key

Hou-san, On Mon, May 24, 2021 at 10:15 PM houzj.f...@fujitsu.com wrote: > From: Amit Langote > Sent: Monday, May 24, 2021 4:27 PM > > On Mon, May 24, 2021 at 10:31 AM houzj.f...@fujitsu.com > > wrote: > > > Currently for a normal RANGE partition key it will first generate a > > > CHECK

RE: Skip partition tuple routing with constant partition key

Hi Amit-san, From: Amit Langote Sent: Monday, May 24, 2021 4:27 PM > Hou-san, > > On Mon, May 24, 2021 at 10:31 AM houzj.f...@fujitsu.com > wrote: > > From: Amit Langote > > Sent: Thursday, May 20, 2021 8:23 PM > > > This one seems bit tough. ExecPartitionCheck() uses the generic > > >

RE: Skip partition tuple routing with constant partition key

From: Hou, Zhijie/侯 志杰 > Ah, Maybe I found the issue. > Attaching a new patch, please have a try on this patch. Thanks, it has compiled perfectly without any warning. Regards Takayuki Tsunakawa

Re: Skip partition tuple routing with constant partition key

Hou-san, On Mon, May 24, 2021 at 10:31 AM houzj.f...@fujitsu.com wrote: > From: Amit Langote > Sent: Thursday, May 20, 2021 8:23 PM > > This one seems bit tough. ExecPartitionCheck() uses the generic expression > > evaluation machinery like a black box, which means execPartition.c can't > >

RE: Skip partition tuple routing with constant partition key

From: houzj.f...@fujitsu.com Sent: Monday, May 24, 2021 3:58 PM > > From: Tsunakawa, Takayuki > Sent: Monday, May 24, 2021 3:34 PM > > > > From: mailto:houzj.f...@fujitsu.com > > > I think this patch can solve the performance

RE: Skip partition tuple routing with constant partition key

From: Hou, Zhijie/侯 志杰 > It seems a little strange, I have compiled it alone in two different linux > machine > and did > not find such an error. Did you compile it on a windows machine ? On Linux, it produces: gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-s\

RE: Skip partition tuple routing with constant partition key

From: Tsunakawa, Takayuki Sent: Monday, May 24, 2021 3:34 PM > > From: houzj.f...@fujitsu.com > > I think this patch can solve the performance degradation of key > > expression after applying the [Save the last partition] patch. > > Besides, this could be a separate patch which can improve some

RE: Skip partition tuple routing with constant partition key

From: houzj.f...@fujitsu.com > I think this patch can solve the performance degradation of key expression > after > applying the [Save the last partition] patch. > Besides, this could be a separate patch which can improve some more cases. > Thoughts ? Thank you for proposing an impressive

RE: Skip partition tuple routing with constant partition key

From: Amit Langote Sent: Thursday, May 20, 2021 8:23 PM > > Hou-san, > > On Thu, May 20, 2021 at 7:35 PM houzj.f...@fujitsu.com > wrote: > > 2) Test expression in partition key > > > > create or replace function partition_func(i int) returns int as $$ > > begin > > return i; > >

Re: Skip partition tuple routing with constant partition key

Hou-san, On Thu, May 20, 2021 at 7:35 PM houzj.f...@fujitsu.com wrote: > From: Amit Langote > Sent: Wednesday, May 19, 2021 9:17 PM > > I guess it would be nice if we could fit in a solution for the use case > > that houjz > > mentioned as a special case. BTW, houjz, could you please check if

RE: Skip partition tuple routing with constant partition key

From: Amit Langote Sent: Wednesday, May 19, 2021 9:17 PM > I gave a shot to implementing your idea and ended up with the attached PoC > patch, which does pass make check-world. > > I do see some speedup: > > -- creates a range-partitioned table with 1000 partitions create unlogged > table >

Re: Skip partition tuple routing with constant partition key

On Thu, 20 May 2021 at 20:49, Amit Langote wrote: > > On Thu, May 20, 2021 at 9:31 AM David Rowley wrote: > > Wondering what your thoughts are on, instead of caching the last used > > ResultRelInfo from the last call to ExecFindPartition(), to instead > > cached the last looked up partition

Re: Skip partition tuple routing with constant partition key

On Thu, May 20, 2021 at 9:20 AM tsunakawa.ta...@fujitsu.com wrote: > From: Amit Langote > > On Tue, May 18, 2021 at 11:11 AM houzj.f...@fujitsu.com > > wrote: > > > For some big data scenario, we sometimes transfer data from one table(only > > store not expired data) > > > to another

Re: Skip partition tuple routing with constant partition key

On Thu, May 20, 2021 at 9:31 AM David Rowley wrote: > On Thu, 20 May 2021 at 01:17, Amit Langote wrote: > > I gave a shot to implementing your idea and ended up with the attached > > PoC patch, which does pass make check-world. > > I only had a quick look at this. > > + if

Re: Skip partition tuple routing with constant partition key

On Thu, 20 May 2021 at 12:20, tsunakawa.ta...@fujitsu.com wrote: > Yes, I want to make/keep it possible that application developers can be > unaware of partitions. I believe that's why David-san, Alvaro-san, and you > have made great efforts to improve partitioning performance. So, I'm +1 for

Re: Skip partition tuple routing with constant partition key

On Thu, 20 May 2021 at 01:17, Amit Langote wrote: > I gave a shot to implementing your idea and ended up with the attached > PoC patch, which does pass make check-world. I only had a quick look at this. + if ((dispatch->key->strategy == PARTITION_STRATEGY_RANGE || + dispatch->key->strategy ==

RE: Skip partition tuple routing with constant partition key

From: Amit Langote > On Tue, May 18, 2021 at 11:11 AM houzj.f...@fujitsu.com > wrote: > > For some big data scenario, we sometimes transfer data from one table(only > store not expired data) > > to another table(historical data) for future analysis. > > In this case, we import data into

Re: Skip partition tuple routing with constant partition key

On Tue, May 18, 2021 at 11:11 AM houzj.f...@fujitsu.com wrote: > > > Hmm, does this seem common enough for the added complexity to be > > worthwhile? > > > > I'd also like to know if there's some genuine use case for this. For testing > > purposes does not seem to be quite a good enough reason. >

Re: Skip partition tuple routing with constant partition key

On Tue, May 18, 2021 at 10:28 AM David Rowley wrote: > On Tue, 18 May 2021 at 01:31, Amit Langote wrote: > > Hmm, does this seem common enough for the added complexity to be worthwhile? > > I'd also like to know if there's some genuine use case for this. For > testing purposes does not seem to

Re: Skip partition tuple routing with constant partition key

On Tue, May 18, 2021 at 01:27:48PM +1200, David Rowley wrote: > A slightly different optimization that I have considered and even > written patches before was to have ExecFindPartition() cache the last > routed to partition and have it check if the new row can go into that > one on the next call.

RE: Skip partition tuple routing with constant partition key

> > Hmm, does this seem common enough for the added complexity to be > worthwhile? > > I'd also like to know if there's some genuine use case for this. For testing > purposes does not seem to be quite a good enough reason. Thanks for the response. For some big data scenario, we sometimes

Re: Skip partition tuple routing with constant partition key

On Tue, 18 May 2021 at 01:31, Amit Langote wrote: > Hmm, does this seem common enough for the added complexity to be worthwhile? I'd also like to know if there's some genuine use case for this. For testing purposes does not seem to be quite a good enough reason. A slightly different

Re: Skip partition tuple routing with constant partition key

On Mon, May 17, 2021 at 8:37 PM houzj.f...@fujitsu.com wrote: > When loading some data into a partitioned table for testing purpose, > > I found even if I specified constant value for the partition key[1], it still > do > > the tuple routing for each row. > > > [1]- > >

Skip partition tuple routing with constant partition key

Hi, When loading some data into a partitioned table for testing purpose, I found even if I specified constant value for the partition key[1], it still do the tuple routing for each row. [1]- UPDATE partitioned set part_key = 2 , … INSERT into partitioned(part_key,