CODEWITHSUNDEEP
postgresqlpartitioningdatabase-partitioning
zeroid
zeroid

Reputation: 731

Why isn't Postgres multicolumn partition pruning smarter than this?

In Postgres (v12), we have some large tables that are partitioned by "country" and "sector", 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 "country" column is somehow favoured in the pruning logic, presumably as it is this first column in the partition key...

Example

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';

Note the same partitions are scanned even if I used 'OR' logic instead of 'IN'. I can add the explain analyse output if needed.

So firstly, why scan ALL 'GB' partitions just because more than one sector is specified?

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.

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 "explicit" in the query.

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?

Upvotes: 1

Views: 1205

Answers (1)

zeroid
zeroid

Reputation: 731

Use of sub-partitions makes the query planner behave much more sensibly. Since this also allows us to partition by list, there is no need to artificially include the integer column in the partition key to range on:

CREATE TABLE part.sub_partitioned_table
(
    country character varying NOT NULL,
    sector character varying NOT NULL,
    a_value integer NOT NULL,
    other_value integer,
    CONSTRAINT sub_partitioned_table_pkey PRIMARY KEY (country, sector, a_value)
) PARTITION BY LIST (country);

CREATE TABLE part.sub_partitioned_table_de PARTITION OF part.sub_partitioned_table
    FOR VALUES IN ('DE')
    PARTITION BY LIST (sector);
CREATE TABLE part.sub_partitioned_table_gb PARTITION OF part.sub_partitioned_table
    FOR VALUES IN ('GB')
    PARTITION BY LIST (sector);
CREATE TABLE part.sub_partitioned_table_fr PARTITION OF part.sub_partitioned_table
    FOR VALUES IN ('FR')
    PARTITION BY LIST (sector);

CREATE TABLE part.sub_partitioned_table_gb_alpha PARTITION OF part.sub_partitioned_table_gb
    FOR VALUES IN ('ALPHA');
CREATE TABLE part.sub_partitioned_table_gb_beta PARTITION OF part.sub_partitioned_table_gb
     FOR VALUES IN ('BETA');
CREATE TABLE part.sub_partitioned_table_gb_gamma PARTITION OF part.sub_partitioned_table_gb
    FOR VALUES IN ('GAMMA');
CREATE TABLE part.sub_partitioned_table_fr_alpha PARTITION OF part.sub_partitioned_table_fr
    FOR VALUES IN ('ALPHA');
CREATE TABLE part.sub_partitioned_table_fr_beta PARTITION OF part.sub_partitioned_table_fr
     FOR VALUES IN ('BETA');
CREATE TABLE part.sub_partitioned_table_fr_gamma PARTITION OF part.sub_partitioned_table_fr
    FOR VALUES IN ('GAMMA');    
CREATE TABLE part.sub_partitioned_table_de_alpha PARTITION OF part.sub_partitioned_table_de
    FOR VALUES IN ('ALPHA');
CREATE TABLE part.sub_partitioned_table_de_beta PARTITION OF part.sub_partitioned_table_de
     FOR VALUES IN ('BETA');
CREATE TABLE part.sub_partitioned_table_de_gamma PARTITION OF part.sub_partitioned_table_de
    FOR VALUES IN ('GAMMA');

INSERT INTO part.sub_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);
    
    
EXPLAIN ANALYSE SELECT * FROM part.sub_partitioned_table WHERE country = 'GB' and sector = 'BETA';

EXPLAIN ANALYSE SELECT * FROM part.sub_partitioned_table WHERE country = 'GB' and sector in ('ALPHA', 'BETA');
EXPLAIN ANALYSE SELECT * FROM part.sub_partitioned_table WHERE country = 'GB' and (sector = 'ALPHA' or sector = 'BETA');

EXPLAIN ANALYSE SELECT * FROM part.sub_partitioned_table WHERE country in ('GB', 'FR') and sector = 'BETA';
EXPLAIN ANALYSE SELECT * FROM part.sub_partitioned_table WHERE (country = 'GB' or country = 'FR') and sector = 'BETA';

EXPLAIN ANALYSE SELECT * FROM part.sub_partitioned_table WHERE
((sector = 'BETA' AND country = 'GB') OR
 (sector = 'BETA' AND country = 'FR'))
 
EXPLAIN ANALYSE SELECT * FROM part.sub_partitioned_table WHERE
((sector = 'BETA' AND country = 'GB') OR
 (sector = 'ALPHA' AND country = 'GB'))

In all cases the query plan shows that only the relevant (sub)partitions are scanned.

Upvotes: 4

Related Questions