Re: [HACKERS] Runtime Partition Pruning

On 4 April 2018 at 05:44, Jesper Pedersen <jesper(dot)pedersen(at)redhat(dot)com> wrote:
> I have tested this together with Amit's v46 patch.

Thanks for testing this.

> The attached case doesn't trigger a generic plan, so basically all time is
> spent in GetCachedPlan.

Yeah, there's still no resolution to the fact that a generic plan +
runtime pruning might be cheaper than a custom plan. The problem is
the generic plan appears expensive to the custom vs generic plan
comparison due to it containing more Append subnodes and the run-time
pruning not being taking into account by that comparison.

There's been some discussion about this on this thread somewhere.

I think the best solution is probably the one suggested by Robert [1]
and that's to alter the Append plan's cost when run-time pruning is
enabled to try to account for the run-time pruning. This would be a
bit of a blind guess akin to what we do for clause selectivity
estimates for Params, but it's probably better than nothing, and
likely better than doing nothing.

> Also, I'm seeing a regression for check-world in
> src/test/regress/results/inherit.out
>
> ***************
> *** 642,648 ****
> ---------------------+---+---+-----
> mlparted_tab_part1 | 1 | a |
> mlparted_tab_part2a | 2 | a |
> ! mlparted_tab_part2b | 2 | b | xxx
> mlparted_tab_part3 | 3 | a | xxx
> (4 rows)
>
> --- 642,648 ----
> ---------------------+---+---+-----
> mlparted_tab_part1 | 1 | a |
> mlparted_tab_part2a | 2 | a |
> ! mlparted_tab_part2b | 2 | b |
> mlparted_tab_part3 | 3 | a | xxx
> (4 rows)
>
> I'll spend some more time tomorrow.

Yeah, it's a bug in v46 faster partition pruning. Discussing a fix for
that with Amit over on [2].

If you patch v46 with the patch I attached to that thread, it should work.

[1] https://www.postgresql.org/message-id/CA%2BTgmoZv8sd9cKyYtHwmd_13%2BBAjkVKo%3DECe7G98tBK5Ejwatw%40mail.gmail.com
[2] https://www.postgresql.org/message-id/CAKJS1f_6%2BgXB%3DQ%2BDryeB62yW7N19sY8hH_dBSjPFjm2ifdgoCw%40mail.gmail.com

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