Re: [HACKERS] path toward faster partition pruning

On 5 January 2018 at 07:16, Jesper Pedersen <jesper(dot)pedersen(at)redhat(dot)com> wrote:
> \set b random(1, 1000000)
> BEGIN;
> SELECT t1.a, t1.b FROM t1 WHERE t1.b = :b;
> COMMIT;
> -- select.sql --
>
> using pgbench -c X -j X -M prepared -T X -f select.sql part-hash
>
> On master we have generic_cost planning cost of 33.75, and an
> avg_custom_cost of 51.25 resulting in use of the generic plan and a TPS of
> 8893.
>
> Using v17 we have generic_cost planning cost of 33.75, and an
> avg_custom_cost of 25.9375 resulting in use of the custom plan and a TPS of
> 7129 - of course due to the generation of a custom plan for each invocation.
>
> Comparing master with an non-partitioned scenario; we have a TPS of 12968,
> since there is no overhead of ExecInitAppend (PortalStart) and ExecAppend
> (PortalRun).
>
> Could you share your thoughts on
>
> 1) if the generic plan mechanics should know about the pruning and hence
> give a lower planner cost

I think the problem here is that cached_plan_cost() is costing the
planning cost of the query too low. If this was costed higher then its
more likely the generic plan would have been chosen, instead of
generating a custom plan each time.

How well does it perform if you change cpu_operator_cost = 0.01?

I think cached_plan_cost() does need an overhaul, but I think it's not
anything that should be done as part of this patch. You've picked HASH
partitioning here just because the current master does not perform any
partition pruning for that partitioning strategy.

There also might be a tiny argument here to have some method of
disabling the planner's partition pruning as we could before with SET
constraint_exclusion = 'off', but I think that's about the limit of
the interest this patch should have in that problem.

(The problem gets more complex again when doing run-time pruning, but
that's not a topic for this thread)

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