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: Amit Langote <Langote_Amit_f8@lab.ntt.co.jp>
Cc: Robert Haas <robertmhaas@gmail.com>, Ashutosh Bapat <ashutosh.bapat@enterprisedb.com>, Rajkumar Raghuwanshi <rajkumar.raghuwanshi@enterprisedb.com>, Kyotaro HORIGUCHI <horiguchi.kyotaro@lab.ntt.co.jp>, Jesper Pedersen <jesper.pedersen@redhat.com>, Amit Langote <amitlangote09@gmail.com>, Dilip Kumar <dilipbalaut@gmail.com>, Beena Emerson <memissemerson@gmail.com>, "pgsql-hackers@postgresql.org" <pgsql-hackers@postgresql.org>
Date: 2018-03-19T10:35:43Z
Lists: pgsql-hackers 
On 19 March 2018 at 23:03, Amit Langote <Langote_Amit_f8@lab.ntt.co.jp> wrote:
> Just recently, I replied to a pgsql-bugs report by someone who had OOM
> kill a backend running `delete from
> partitioned_table_with_7202_partitions` on their test system [1].  That'd
> be because running inheritance_planner on a partitioned table doesn't cope
> very well beyond a few hundred partitions, as we've also written in our
> partitioning/inheritance documentation.

hmm, yeah that's unfortunate. I'd not done much study of the
inheritance planner before, but I see how that could happen now that I
understand a bit more about it.  nparts^2 RelOptInfos will be created
on such problems. My patch should help with that providing that some
pruning will actually take place, but make the problem very slightly
worse if none can be pruned.

> On 2018/03/19 16:18, David Rowley wrote:
>> Likely there's more than could be squeezed out of this if we could get
>> the grouping_planner() to somehow skip creating paths and performing
>> the join search. But that patch is not nearly as simple as the
>> attached.
>
> Yeah, that'd be nice.  Do you think that we cannot fix update/delete on
> partitioned tables until we have such a patch though?  IOW, did you intend
> the patch you posted to just be a PoC to demonstrate that we can save tons
> just by not doing grouping_planner() on pruned partitions?

The patch was meant as a PoC. I think the performance of the patch is
acceptable without any additional optimisation work. It would be nice,
but any more code that's added would need more reviewer and committer
time, both of which are finite, especially so before PG11 code cut
off.

I think it would be a shame to tell people partition is usable now for
a decent number of partitions, providing you don't need to perform any
OLTP UPDATE/DELETE operations on the partitions. I think for the few
lines of code that the proposed patch takes it's worth considering for
PG11, but only once your work has gone in. I certainly wouldn't want
this to hold your work back.

> BTW, maybe you know, but if we want this to prune same partitions as are
> pruned during select (due to the new pruning facility), we'd need to teach
> get_relation_constraints() to not fetch the partition constraint
> (RelationGetPartitionQual) at all.  My patch currently teaches it to avoid
> fetching the partition constraint only for select.  If we include the
> partition constraint in the list of constraints returned by
> get_relation_constraints, we'd still be redundantly executing the
> constraint exclusion logic for the selected partitions via the
> grouping_planner() call on those partitions.

I'd not thought of that. It seems more like a performance optimisation
than something that's required for correctness.  Removing that would
probably make constraint_exclusion = 'partition' pretty useless


-- 
 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.