Re: path toward faster partition pruning

Amit Langote <langote_amit_f8@lab.ntt.co.jp>

View thread View on postgr.es

From: Amit Langote <Langote_Amit_f8@lab.ntt.co.jp>

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

Cc: Dilip Kumar <dilipbalaut@gmail.com>, Pg Hackers <pgsql-hackers@postgresql.org>

Date: 2017-09-27T01:22:40Z

Lists: pgsql-hackers

Attachments

0001-WIP-planner-side-changes-for-partition-pruning.patch (text/plain) patch 0001
0002-WIP-Interface-changes-for-partition_bound_-cmp-bsear.patch (text/plain) patch 0002
0003-WIP-Implement-get_partitions_for_keys.patch (text/plain) patch 0003
0004-Add-more-tests-for-the-new-partitioning-related-plan.patch (text/plain) patch 0004

Hi David,

On 2017/09/27 6:04, David Rowley wrote:
> On 25 September 2017 at 23:04, Amit Langote
> <Langote_Amit_f8@lab.ntt.co.jp> wrote:
>> By the way, I'm now rebasing these patches on top of [1] and will try to
>> merge your refactoring patch in some appropriate way.  Will post more
>> tomorrow.
>>
>> [1] https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=9140cf826
> 
> Yeah, I see 0001 conflicts with that. I'm going to set this to waiting
> on author while you're busy rebasing this.

Thanks for the reminder.  Just thought I'd say that while I'm actually
done rebasing itself (attaching rebased patches to try 'em out), I'm now
considering Robert's comments and will be busy for a bit revising things
based on those comments.

Some notes about the attached patches:

- 0001 includes refactoring that Dilip proposed upthread [1] (added him as
  an author).  I slightly tweaked his patch -- renamed the function
  get_matching_clause to match_clauses_to_partkey, similar to
  match_clauses_to_index.

- Code to set AppendPath's partitioned_rels in add_paths_to_append_rel()
  revised by 0a480502b09 (was originally introduced in d3cc37f1d80) is
  still revised to get partitioned_rels from a source that is not
  PlannerInfo.pcinfo_list.  With the new code, partitioned_rels won't
  contain RT indexes of the partitioned child tables that were pruned.


Thanks,
Amit

[1]
https://www.postgresql.org/message-id/CAFiTN-tGnQzF_4QtbOHT-3hE%3DOvNaMfbbeRxa4UY0CQyF0G8gQ%40mail.gmail.com

Commits

Fix assorted partition pruning bugs
- d758d9702e2f 11.0 landed
Make gen_partprune_steps static
- d1e2cac5ff7e 11.0 landed
Remove useless 'default' clause
- c775fb9e18ac 11.0 landed
Reorganize partitioning code
- da6f3e45ddb6 11.0 landed
Use custom hash opclass for hash partition pruning
- fafec4cce814 11.0 landed
Blindly attempt to fix sepgsql tests broken due to 9fdb675fc5.
- 4f813c7203e0 11.0 landed
Attempt to fix endianess issues in new hash partition test.
- 40e42e1024c5 11.0 landed
Faster partition pruning
- 9fdb675fc5d2 11.0 landed
For partitionwise join, match on partcollation, not parttypcoll.
- 2af28e603319 11.0 landed
Revise API for partition bound search functions.
- f724022d0ae0 11.0 landed
Revise API for partition_rbound_cmp/partition_rbound_datum_cmp.
- b0229235564f 11.0 landed
Fix possible crash in partition-wise join.
- f069c91a5793 11.0 cited
Refactor code for partition bound searching
- 9aef173163ae 11.0 landed
New C function: bms_add_range
- 84940644de93 11.0 landed
Add extensive tests for partition pruning.
- 8d4e70a63bf8 11.0 landed
Add null test to partition constraint for default range partitions.
- 7b88d63a9122 11.0 cited
Remove BufFile's isTemp flag.
- 11e264517dff 11.0 cited
Make OWNER TO subcommand mention consistent
- bf54c0f05c0a 11.0 cited
Fix index matching for operators with mixed collatable/noncollatable inputs.
- cb37c291060d 9.2.0 cited