Waiting for PostgreSQL 11 – Improve run-time partition pruning to handle any stable expression.

On 10th of June 2018, Tom Lane committed patch:

Improve run-time partition pruning to handle any stable expression.
 
 
The initial coding of the run-time-pruning feature only coped with cases
where the partition key(s) are compared to Params.  That is a bit silly;
we can allow it to work with any non-Var-containing stable expression, as
long as we take special care with expressions containing PARAM_EXEC Params.
The code is hardly any longer this way, and it's considerably clearer
(IMO at least).  Per gripe from Pavel Stehule.
 
David Rowley, whacked around a bit by me
 
Discussion: https://postgr.es/m/CAFj8pRBjrufA3ocDm8o4LPGNye9Y+pm1b9kCwode4X04CULG3g@mail.gmail.com

This is basically extension of work that I described earlier.

Previously, it looked that the pruning can happen only in some specific cases, which were rather tricky to explain.

Now, however, the pruning happens when you use any stable expression. For example – call to function that is marked stable or immutable.

Let's recreate our test case:

=$ CREATE TABLE users (
    id             serial NOT NULL,
    username       text NOT NULL,
    password       text
)
PARTITION BY RANGE ( id );
 
=$ CREATE TABLE users_0
    partition OF users (id, PRIMARY KEY (id), UNIQUE (username))
    FOR VALUES FROM (minvalue) TO (10);
 
=$ CREATE TABLE users_1
    partition OF users (id, PRIMARY KEY (id), UNIQUE (username))
    FOR VALUES FROM (10) TO (20);
=$ CREATE TABLE users_2
    partition OF users (id, PRIMARY KEY (id), UNIQUE (username))
    FOR VALUES FROM (20) TO (30);
=$ CREATE TABLE users_3
    partition OF users (id, PRIMARY KEY (id), UNIQUE (username))
    FOR VALUES FROM (30) TO (maxvalue);
 
=$ INSERT INTO users (username) SELECT 'u:' || i::text FROM generate_series(1,25) i;
 
=$ CREATE TABLE x (u_id INT4);
 
=$ INSERT INTO x (u_id) VALUES (13);
 
=$ ANALYZE;

Now, without this patch, if I'd try something like this:

=$ EXPLAIN analyze SELECT * FROM users WHERE id = least(15,20);

I would get plan that checks all partitions:

                                                         QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------
 Append  (cost=0.00..11.56 ROWS=4 width=68) (actual TIME=0.007..0.012 ROWS=1 loops=1)
   ->  Seq Scan ON users_0  (cost=0.00..1.14 ROWS=1 width=68) (actual TIME=0.005..0.005 ROWS=0 loops=1)
         FILTER: (id = LEAST(15, 20))
         ROWS Removed BY FILTER: 9
   ->  Seq Scan ON users_1  (cost=0.00..1.15 ROWS=1 width=68) (actual TIME=0.002..0.002 ROWS=1 loops=1)
         FILTER: (id = LEAST(15, 20))
         ROWS Removed BY FILTER: 9
   ->  Seq Scan ON users_2  (cost=0.00..1.09 ROWS=1 width=68) (actual TIME=0.001..0.001 ROWS=0 loops=1)
         FILTER: (id = LEAST(15, 20))
         ROWS Removed BY FILTER: 6
   ->  INDEX Scan USING users_3_pkey ON users_3  (cost=0.15..8.17 ROWS=1 width=68) (actual TIME=0.003..0.003 ROWS=0 loops=1)
         INDEX Cond: (id = LEAST(15, 20))
 Planning TIME: 0.395 ms
 Execution TIME: 0.122 ms
(14 ROWS)

But now, I get much nicer:

                                               QUERY PLAN
--------------------------------------------------------------------------------------------------------
 Append  (cost=0.00..11.56 ROWS=4 width=68) (actual TIME=0.016..0.019 ROWS=1 loops=1)
   Subplans Removed: 3
   ->  Seq Scan ON users_1  (cost=0.00..1.15 ROWS=1 width=68) (actual TIME=0.015..0.017 ROWS=1 loops=1)
         FILTER: (id = LEAST(15, 20))
         ROWS Removed BY FILTER: 9
 Planning TIME: 1.949 ms
 Execution TIME: 0.435 ms
(7 ROWS)

Of course my example is very simplistic, but I hope it shows exactly what is the new hotness and what is the benefit of it.

Thanks a lot 🙂

One thought on “Waiting for PostgreSQL 11 – Improve run-time partition pruning to handle any stable expression.”

Comments are closed.