title: Why isn't Postgres multicolumn partition pruning smarter than this?
body: <p>In Postgres (v12), we have some large tables that are partitioned by &quot;country&quot; and &quot;sector&quot;, but have found that the pruning behaviour is only optimal for queries that identify an exact value for each, and becomes sub-optimal when 2 or more values are specified for either.  In addition the &quot;country&quot; column is somehow favoured in the pruning logic, presumably as it is this first column in the partition key...</p>
  <p>Example</p>
  <pre class="lang-sql prettyprint-override"><code>CREATE TABLE part.partitioned_table
  (
      country character varying NOT NULL,
      sector character varying NOT NULL,
      a_value integer NOT NULL,
      other_value integer,
      CONSTRAINT partitioned_table_pkey PRIMARY KEY (country, sector, a_value)
  ) PARTITION BY RANGE (country, sector, a_value);
  
  CREATE TABLE part.partitioned_table_gb_alpha PARTITION OF part.partitioned_table
      FOR VALUES FROM ('GB', 'ALPHA', MINVALUE) TO ('GB', 'ALPHA', MAXVALUE);
  CREATE TABLE part.partitioned_table_gb_beta PARTITION OF part.partitioned_table
      FOR VALUES FROM ('GB', 'BETA', MINVALUE) TO ('GB', 'BETA', MAXVALUE);
  CREATE TABLE part.partitioned_table_gb_gamma PARTITION OF part.partitioned_table
      FOR VALUES FROM ('GB', 'GAMMA', MINVALUE) TO ('GB', 'GAMMA', MAXVALUE);
  CREATE TABLE part.partitioned_table_fr_alpha PARTITION OF part.partitioned_table
      FOR VALUES FROM ('FR', 'ALPHA', MINVALUE) TO ('FR', 'ALPHA', MAXVALUE);
  CREATE TABLE part.partitioned_table_fr_beta PARTITION OF part.partitioned_table
      FOR VALUES FROM ('FR', 'BETA', MINVALUE) TO ('FR', 'BETA', MAXVALUE);
  CREATE TABLE part.partitioned_table_fr_gamma PARTITION OF part.partitioned_table
      FOR VALUES FROM ('FR', 'GAMMA', MINVALUE) TO ('FR', 'GAMMA', MAXVALUE);
  CREATE TABLE part.partitioned_table_de_alpha PARTITION OF part.partitioned_table
      FOR VALUES FROM ('DE', 'ALPHA', MINVALUE) TO ('DE', 'ALPHA', MAXVALUE);
  CREATE TABLE part.partitioned_table_de_beta PARTITION OF part.partitioned_table
      FOR VALUES FROM ('DE', 'BETA', MINVALUE) TO ('DE', 'BETA', MAXVALUE);
  CREATE TABLE part.partitioned_table_de_gamma PARTITION OF part.partitioned_table
      FOR VALUES FROM ('DE', 'GAMMA', MINVALUE) TO ('DE', 'GAMMA', MAXVALUE);
  
  INSERT INTO part.partitioned_table(country, sector, a_value, other_value) VALUES
      ('GB', 'ALPHA', 10, 1000),
      ('GB', 'BETA', 10, 1000),
      ('GB', 'GAMMA', 10, 1000),
      ('FR', 'ALPHA', 10, 1000),
      ('FR', 'BETA', 10, 1000),
      ('FR', 'GAMMA', 10, 1000),
      ('DE', 'ALPHA', 10, 1000),
      ('DE', 'BETA', 10, 1000),
      ('DE', 'GAMMA', 10, 1000);
  
  -- query plan for this statement shows that only a single partition is scanned as expected (partitioned_table_gb_beta)
  EXPLAIN ANALYSE SELECT * FROM part.partitioned_table WHERE country = 'GB' and sector = 'BETA';
  
  -- adding a sector to the where clause causes ALL 'GB' partitions to be scanned
  EXPLAIN ANALYSE SELECT * FROM part.partitioned_table WHERE country = 'GB' and sector in ('BETA', 'GAMMA');
  
  -- instead adding a country to the where clause causes ALL 'GB' and 'FR' partitions to be scanned!
  EXPLAIN ANALYSE SELECT * FROM part.partitioned_table WHERE country in ('GB', 'FR') and sector = 'BETA';
  </code></pre>
  <p>Note the same partitions are scanned even if I used 'OR' logic instead of 'IN'.  I can add the <code>explain analyse</code> output if needed.</p>
  <p>So firstly, why scan ALL 'GB' partitions just because more than one sector is specified?</p>
  <p>And secondly, perhaps more strangely, if I stick to a single sector ('BETA' in my example) and add a second country, instead of it scanning all the 'BETA' partitions, it now scans all the partitions for each specified country.</p>
  <p>Obviously the real world table is hundreds of countries and sectors.  We have use cases to query a single sector across multiple countries (e.g. let's say 20), and end up scanning hundreds of partitions (all the sector partitions for those 20 countries), when only 20 partitions need to be scanned and this is &quot;explicit&quot; in the query.</p>
  <p>Would we need to create another version of the table partitioned primarily by sector to overcome this or is there something else we've missed here?</p>