Re: Record a Bitmapset of non-pruned partitions

On Mon, 2 Aug 2021 at 00:31, David Rowley <dgrowleyml@gmail.com> wrote:
> I had another self review of these and I'm pretty happy with them. I'm
> quite glad to see the performance of querying a single partition of a
> table with large numbers of partitions no longer tails off as much as
> it used to.

I did some profiling and benchmarking on master and with the v4 patch.
With a hash partitioned table containing 8192 partitions I see the
following when running a query that selects a value from a single
partition:

  19.39%  postgres            [.] apply_scanjoin_target_to_paths
   5.35%  postgres            [.] base_yyparse
   4.71%  postgres            [.] AllocSetAlloc
   2.86%  libc-2.33.so        [.] __memset_avx2_unaligned_erms
   2.17%  postgres            [.] SearchCatCacheInternal

With the patched version, I see:

   5.89%  postgres            [.] AllocSetAlloc
   3.97%  postgres            [.] base_yyparse
   3.87%  libc-2.33.so        [.] __memset_avx2_unaligned_erms
   2.44%  postgres            [.] SearchCatCacheInternal
   1.29%  postgres            [.] hash_search_with_hash_value

I'm getting:
master: 16613 tps
patched: 22078 tps

So there's about 32% performance improvement with this number of
partitions. These results are not the same as my original email here
as I've only recently discovered that I really need to pin pgbench and
the postgres backend to the same CPU core to get good and stable
performance from a single threaded pgbench job.

FWIW, the next thing there on the profile the following line in
expand_partitioned_rtentry()

relinfo->part_rels = (RelOptInfo **) palloc0(relinfo->nparts *
sizeof(RelOptInfo *));

If anyone has any objections to both the v4 0001 and 0002 patch, can
they let me know soon. I'm currently seeing no reason that they can't
go in.

David