Horton

Re: [HACKERS] path toward faster partition pruning

David Rowley <david.rowley@2ndquadrant.com>

View thread View on postgr.es
From: David Rowley <david.rowley@2ndquadrant.com>
To: jesper.pedersen@redhat.com
Cc: Amit Langote <Langote_Amit_f8@lab.ntt.co.jp>, Kyotaro HORIGUCHI <horiguchi.kyotaro@lab.ntt.co.jp>, Rajkumar Raghuwanshi <rajkumar.raghuwanshi@enterprisedb.com>, Robert Haas <robertmhaas@gmail.com>, Dilip Kumar <dilipbalaut@gmail.com>, Beena Emerson <memissemerson@gmail.com>, PostgreSQL-development <pgsql-hackers@postgresql.org>
Date: 2018-01-05T02:21:47Z
Lists: pgsql-hackers 
On 5 January 2018 at 07:16, Jesper Pedersen <jesper.pedersen@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

Commits

  1. Fix assorted partition pruning bugs

  2. Make gen_partprune_steps static

  3. Remove useless 'default' clause

  4. Reorganize partitioning code

  5. Use custom hash opclass for hash partition pruning

  6. Blindly attempt to fix sepgsql tests broken due to 9fdb675fc5.

  7. Attempt to fix endianess issues in new hash partition test.

  8. Faster partition pruning

  9. For partitionwise join, match on partcollation, not parttypcoll.

  10. Revise API for partition bound search functions.

  11. Revise API for partition_rbound_cmp/partition_rbound_datum_cmp.

  12. Fix possible crash in partition-wise join.

  13. Refactor code for partition bound searching

  14. New C function: bms_add_range

  15. Add extensive tests for partition pruning.

  16. Add null test to partition constraint for default range partitions.

  17. Remove BufFile's isTemp flag.

  18. Make OWNER TO subcommand mention consistent

  19. Fix index matching for operators with mixed collatable/noncollatable inputs.