Re: Make Append Cost aware of some run time partition prune case

Hi Ryan:

On Thu, Mar 4, 2021 at 8:14 AM Ryan Lambert <ryan(at)rustprooflabs(dot)com> wrote:

> On Mon, Nov 9, 2020 at 5:44 PM Andy Fan <zhihui(dot)fan1213(at)gmail(dot)com> wrote:
>
>> Currently the cost model of append path sums the cost/rows for all the
>> subpaths, it usually works well until we run into the run-time partition
>> prune
>> case. The first result is that generic plans will rarely be used for
>> some cases.
>> For instance, SELECT * FROM p WHERE pkey = $1; The custom plan will only
>> count the cost of one partition, however generic plan will count the cost
>> for all the
>> partitions even we are sure that only 1 partition will survive. Another
>> impact
>> is that planners may choose a wrong plan. for example, SELECT * FROM
>> t1, p
>> WHERE t1.a = p.pkey; The cost/rows of t1 nest loop p is estimated highly
>> improperly. This patch wants to help this case to some extent.
>>
>
> Greetings,
>
> I was referred to this patch by Amit as a possible improvement for an
> issue I noticed recently. I had a test setup where I expected run-time
> pruning to kick in but it did not. I am trying to test this patch to see
> if it helps for that scenario, but ran into an error running make install
> against the current master (commit 0a687c8f1).
>
> costsize.c: In function ‘cost_append’:
> costsize.c:2171:32: error: ‘AppendPath’ {aka ‘struct AppendPath’} has no
> member named ‘partitioned_rels’
> 2171 | List *partitioned_rels = apath->partitioned_rels;
> | ^~
> make[4]: *** [<builtin>: costsize.o] Error 1
> make[4]: Leaving directory
> '/var/lib/postgresql/git/postgresql/src/backend/optimizer/path'
> make[3]: *** [../../../src/backend/common.mk:39: path-recursive] Error 2
> make[3]: Leaving directory
> '/var/lib/postgresql/git/postgresql/src/backend/optimizer'
> make[2]: *** [common.mk:39: optimizer-recursive] Error 2
> make[2]: Leaving directory '/var/lib/postgresql/git/postgresql/src/backend'
> make[1]: *** [Makefile:42: install-backend-recurse] Error 2
> make[1]: Leaving directory '/var/lib/postgresql/git/postgresql/src'
> make: *** [GNUmakefile:11: install-src-recurse] Error 2
>
> Thanks,
>
> Ryan Lambert
>
>

Thanks for checking. This patch is on a very old master and the code is
too complex
since I wanted to handle a full scenario of a run time partition prune,
which has lots
of troubles and not very practical I think. so I am not happy with that
now.

I have implemented a new one, which only handles 1 level of partitioned
table, and
only 1 partition key. and only handle the eq operators like partkey = $1 /
partkey in ($1, $2)
/ parkey = $1 or partkey = $2; The patch works well in my user case. I
can send
one on the latest master shortly, and hope it is helpful for you as well.

(At the same time, I also ran into a case that we can expand more init
partition
prune case [1], you can check that one if you like. I am happy with that
patch
now).

[1]
https://www.postgresql.org/message-id/flat/CAKU4AWq4NLxu5JF9%2Bd%3Do%3DA636-%3DeFNFmPx%2BkJ44ezTm%3DikZ73w%40mail.gmail.com

--
Best Regards
Andy Fan (https://www.aliyun.com/)