On 5 January 2018 at 07:16, Jesper Pedersen <jesper.peder...@redhat.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
