Re: Skip partition tuple routing with constant partition key - Mailing list pgsql-hackers

From Amit Langote
Subject Re: Skip partition tuple routing with constant partition key
Date
Msg-id CA+HiwqGjb_O8qn-uL1=z6ri=fnD35MDMiA4yHunfsgY3=yv=Hw@mail.gmail.com
Whole thread Raw
In response to RE: Skip partition tuple routing with constant partition key  ("houzj.fnst@fujitsu.com" <houzj.fnst@fujitsu.com>)
Responses RE: Skip partition tuple routing with constant partition key
List pgsql-hackers
Hou-san,

On Mon, May 24, 2021 at 10:31 AM houzj.fnst@fujitsu.com
<houzj.fnst@fujitsu.com> wrote:
> From: Amit Langote <amitlangote09@gmail.com>
> 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 really
> > tweal/control the time spent evaluating partition constraints.  Given that, we
> > may have to disable the caching when key->partexprs != NIL, unless we can
> > reasonably do what you are suggesting.[]
>
> I did some research on the CHECK expression that ExecPartitionCheck() execute.

Thanks for looking into this and writing the patch.  Your idea does
sound promising.

> Currently for a normal RANGE partition key it will first generate a CHECK expression
> like : [Keyexpression IS NOT NULL AND Keyexpression > lowboud AND Keyexpression < lowboud].
> In this case, Keyexpression will be re-executed which will bring some overhead.
>
> Instead, I think we can try to do the following step:
> 1)extract the Keyexpression from the CHECK expression
> 2)evaluate the key expression in advance
> 3)pass the result of key expression to do the partition CHECK.
> In this way ,we only execute the key expression once which looks more efficient.

I would have preferred this not to touch anything but
ExecPartitionCheck(), at least in the first version.  Especially,
seeing that your patch touches partbounds.c makes me a bit nervous,
because the logic there is pretty complicated to begin with.

How about we start with something like the attached?  It's the same
idea AFAICS, but implemented with a smaller footprint.  We can
consider teaching relcache about this as the next step, if at all.  I
haven't measured the performance, but maybe it's not as fast as yours,
so will need some fine-tuning.  Can you please give it a read?



--
Amit Langote
EDB: http://www.enterprisedb.com

Attachment

pgsql-hackers by date:

Previous
From: "houzj.fnst@fujitsu.com"
Date:
Subject: RE: Skip partition tuple routing with constant partition key
Next
From: "tsunakawa.takay@fujitsu.com"
Date:
Subject: RE: Skip partition tuple routing with constant partition key