Partitioning: Investigate increase in planning time (with PG11 and PG12)

This is a follow-up to #201871 (comment 301884463):

A comparison for planning time for the good and the bad case:

  1. Good: Partitioning key can be used
  2. Bad: No partitioning key present, scan all partitions

In this sheet we can see the planning time over the number of partitions attached (median time across 5 attempts).

The non-partitioned case has a planning time of about 10-12ms.

In the good case (1), we see a slight overhead for the planning compared to the non-partitioned case but otherwise planning time is stable and not dependent on the number of partitions (up to the 64 partitions we have in this example).

In the bad case (2), planning time is linear in the number of partitions attached and goes through the roof. We end up spending 600ms in planning this query with 64 partitions attached.

In this issue we investigate the planning time problem further:

  • PG12 has seen improvements for partitioning. Does it handle the bad case better, too?
  • Refresh statistics on partitioned table after changing attached partitions.
  • Why is the complex query so much more affected by this than the simple query (see the sheet for the examples)?
  • Does statistic size have any effect on the bad case? Decrease default_statistics_target and re-test.
  • Check in a local gdk environment, too (we don't necessarily need data for the planning).

The data from the sheet is based on results from https://gitlab.com/abrandl/gitlab-issue-partitioning/-/blob/master/planning_times.rb. It's been run on a PG11 cluster, production instance size and a data snapshot.

This page may contain information related to upcoming products, features and functionality. It is important to note that the information presented is for informational purposes only, so please do not rely on the information for purchasing or planning purposes. Just like with all projects, the items mentioned on the page are subject to change or delay, and the development, release, and timing of any products, features, or functionality remain at the sole discretion of GitLab Inc.

Edited by 🤖 GitLab Bot 🤖