Re: [HACKERS] path toward faster partition pruning

[Jesper Pedersen]

Hi Amit,

On 11/22/2017 03:59 AM, Amit Langote wrote:
Fixed in the attached.  No other changes beside that.

I have been using the following script to look at the patch

-- test.sql --

CREATE TABLE t1 (
    a integer NOT NULL,
    b integer NOT NULL
) PARTITION BY HASH (b);

CREATE TABLE t1_p00 PARTITION OF t1 FOR VALUES WITH (MODULUS 4, 
REMAINDER 0);
CREATE TABLE t1_p01 PARTITION OF t1 FOR VALUES WITH (MODULUS 4, 
REMAINDER 1);
CREATE TABLE t1_p02 PARTITION OF t1 FOR VALUES WITH (MODULUS 4, 
REMAINDER 2);
CREATE TABLE t1_p03 PARTITION OF t1 FOR VALUES WITH (MODULUS 4, 
REMAINDER 3);

CREATE INDEX idx_t1_b_a_p00 ON t1_p00 USING btree (b, a);
CREATE INDEX idx_t1_b_a_p01 ON t1_p01 USING btree (b, a);
CREATE INDEX idx_t1_b_a_p02 ON t1_p02 USING btree (b, a);
CREATE INDEX idx_t1_b_a_p03 ON t1_p03 USING btree (b, a);

CREATE TABLE t2 (
    c integer NOT NULL,
    d integer NOT NULL
) PARTITION BY HASH (d);

CREATE TABLE t2_p00 PARTITION OF t2 FOR VALUES WITH (MODULUS 4, 
REMAINDER 0);
CREATE TABLE t2_p01 PARTITION OF t2 FOR VALUES WITH (MODULUS 4, 
REMAINDER 1);
CREATE TABLE t2_p02 PARTITION OF t2 FOR VALUES WITH (MODULUS 4, 
REMAINDER 2);
CREATE TABLE t2_p03 PARTITION OF t2 FOR VALUES WITH (MODULUS 4, 
REMAINDER 3);

CREATE INDEX idx_t2_c_p00 ON t2_p00 USING btree (c);
CREATE INDEX idx_t2_c_p01 ON t2_p01 USING btree (c);
CREATE INDEX idx_t2_c_p02 ON t2_p02 USING btree (c);
CREATE INDEX idx_t2_c_p03 ON t2_p03 USING btree (c);

CREATE INDEX idx_t2_d_p00 ON t2_p00 USING btree (d);
CREATE INDEX idx_t2_d_p01 ON t2_p01 USING btree (d);
CREATE INDEX idx_t2_d_p02 ON t2_p02 USING btree (d);
CREATE INDEX idx_t2_d_p03 ON t2_p03 USING btree (d);

INSERT INTO t1 (SELECT i, i FROM generate_series(1, 10000) AS i);
INSERT INTO t2 (SELECT i, i FROM generate_series(1, 10000) AS i);

ANALYZE;

EXPLAIN (ANALYZE) SELECT t1.a, t1.b FROM t1 WHERE t1.b = 1;

EXPLAIN (ANALYZE) SELECT t1.a, t1.b, t2.c, t2.d FROM t1 INNER JOIN t2 ON 
t2.c = t1.b WHERE t2.d = 1;

BEGIN;
EXPLAIN (ANALYZE) UPDATE t1 SET a = 1 WHERE b = 1;
ROLLBACK;

BEGIN;
EXPLAIN (ANALYZE) DELETE FROM t1 WHERE b = 1;
ROLLBACK;

-- test.sql --

I just wanted to highlight that the "JOIN ON" partition isn't pruned - 
the "WHERE" one is.

Should pruning of partitions for UPDATEs (where the partition key isn't 
updated) and DELETEs be added to the TODO list ?

Thanks for working on this !

Best regards,
 Jesper