PostgreSQL doesn't prune partitions correctly if I replace a numeric literal in the where clause with a simple query that returns 1 row

Question

fast query

select ...
from table1 t1
join table2 t2 on t2.org_id = t1.org_id
where t1.org_id = 1

slow query

select ...
from table1 t1
join table2 t2 on t2.org_id = t1.org_id
where t1.org_id = (select org_id from table3 where org_name = "abc" limit 1)

The only difference in the two queries is a substitution of a sub query for the literal. I've tried this on PostgreSQL 12.2 and 11.6 on AWS with RDS. table1 and table2 are both partitioned on the org_id column. table3 has a primary key of org_id and a unique index on org_name. "limit 1" was added to the slow query's sub query to try to help the optimizer.

fast query returns in 10 second for most orgs. slow query takes 30 - 100 seconds for most orgs.

I've tried partition sizes of 128, 256, 384, 512, 1024, 2048, and 4096, with 384 being the best.

The fast query's explain analyze plan is 15 lines and correctly uses only 1 partition. The slow query's explain plan is 2,388 lines for 384 partitions and appears to use only 1 partition but it considers all partitions.

The parser knows the partition key value in the fast query but it cannot know it for the slow query. The only way it can know the partition key is to probe all partitions. If you have a a global index on org_name, then you'll have to execute the correlated subquery first and then supply the returned org_id to the second query (i.e., the fast one). — Jeff Holt, Apr 20 '20 at 02:49
Jeff, what do you mean by "probe all partitions"? According to documentation Postgres should be performing partition pruning at execution time on both of these queries. See this blog posting: https://www.2ndquadrant.com/en/blog/postgresql-12-partitioning/ and official documentation here: https://www.postgresql.org/docs/12/ddl-partitioning.html#DDL-PARTITION-PRUNING — KenFar, Apr 20 '20 at 03:28

pifor · Answer 1 · 2020-04-20T07:44:09.920

You can try to create a SQL stable function to replace the subquery. I have following scenario with PostgreSQL 12.2:

EXPLAIN ANALYZE 
select * 
from table1 t1
join table2 t2 on t2.org_id = t1.org_id
where t1.org_id = 1;
                                                   QUERY PLAN                                                   
----------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=0.00..67.87 rows=78 width=44) (actual time=0.017..0.017 rows=0 loops=1)
   ->  Seq Scan on table2 t2  (cost=0.00..41.88 rows=13 width=4) (actual time=0.011..0.012 rows=1 loops=1)
         Filter: (org_id = 1)
   ->  Materialize  (cost=0.00..25.03 rows=6 width=40) (actual time=0.003..0.003 rows=0 loops=1)
         ->  Seq Scan on part1 t1  (cost=0.00..25.00 rows=6 width=40) (actual time=0.001..0.002 rows=0 loops=1)
               Filter: (org_id = 1)
 Planning Time: 0.432 ms
 Execution Time: 0.046 ms
(8 rows)


EXPLAIN ANALYZE 
select * 
from table1 t1
join table2 t2 on t2.org_id = t1.org_id
where t1.org_id = (select org_id from table3 where org_name = 'abc' limit 1);
                                                   QUERY PLAN                                                   
----------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=4.31..176.25 rows=390 width=44) (actual time=0.023..0.023 rows=0 loops=1)
   InitPlan 1 (returns $0)
     ->  Limit  (cost=0.00..4.31 rows=1 width=4) (actual time=0.013..0.013 rows=1 loops=1)
           ->  Seq Scan on table3  (cost=0.00..25.88 rows=6 width=4) (actual time=0.010..0.010 rows=1 loops=1)
                 Filter: (org_name = 'abc'::text)
   ->  Append  (cost=0.00..125.15 rows=30 width=40) (actual time=0.022..0.023 rows=0 loops=1)
         ->  Seq Scan on part1 t1  (cost=0.00..25.00 rows=6 width=40) (actual time=0.002..0.002 rows=0 loops=1)
               Filter: (org_id = $0)
         ->  Seq Scan on part2 t1_1  (cost=0.00..25.00 rows=6 width=40) (never executed)
               Filter: (org_id = $0)
         ->  Seq Scan on part3 t1_2  (cost=0.00..25.00 rows=6 width=40) (never executed)
               Filter: (org_id = $0)
         ->  Seq Scan on part4 t1_3  (cost=0.00..25.00 rows=6 width=40) (never executed)
               Filter: (org_id = $0)
         ->  Seq Scan on part5 t1_4  (cost=0.00..25.00 rows=6 width=40) (never executed)
               Filter: (org_id = $0)
   ->  Materialize  (cost=0.00..41.94 rows=13 width=4) (never executed)
         ->  Seq Scan on table2 t2  (cost=0.00..41.88 rows=13 width=4) (never executed)
               Filter: (org_id = $0)
 Planning Time: 0.397 ms
 Execution Time: 0.129 ms
(21 rows)

create function f_get_org_id() returns int
language sql
stable
as
$$
select org_id from table3 where org_name = 'abc' limit 1
$$
;
CREATE FUNCTION

EXPLAIN ANALYZE 
select * 
from table1 t1
join table2 t2 on t2.org_id = t1.org_id
where t1.org_id = f_get_org_id()
                                                   QUERY PLAN                                                    
-----------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=0.00..2309.43 rows=390 width=44) (actual time=0.003..0.003 rows=0 loops=1)
   ->  Append  (cost=0.00..1625.15 rows=30 width=40) (actual time=0.003..0.003 rows=0 loops=1)
         Subplans Removed: 4
         ->  Seq Scan on part1 t1  (cost=0.00..325.00 rows=6 width=40) (actual time=0.002..0.002 rows=0 loops=1)
               Filter: (org_id = f_get_org_id())
   ->  Materialize  (cost=0.00..679.44 rows=13 width=4) (never executed)
         ->  Seq Scan on table2 t2  (cost=0.00..679.38 rows=13 width=4) (never executed)
               Filter: (org_id = f_get_org_id())
 Planning Time: 0.655 ms
 Execution Time: 0.091 ms
(10 rows)

Thanks but it didn't speed up the query. It still spends time on each partition when it should know to only use 1:-> Index Scan using table1_part_0_pkey on table1_part_0 v1 (cost=0.67..56.08 rows=15 width=203) (actual time=0.649..0.649 rows=0 loops=1) Index Cond: (org_id = f_get_org_id('abc'::character varying)) ... -> Index Scan using table1_part_383_pkey on table1_part_383 v1_383 (cost=0.67..74.43 rows=18 width=170) (actual time=0.159..0.159 rows=0 loops=1) Index Cond: (org_id = f_get_org_id('abc'::character varying)) Planning Time: 1348.134 ms Execution Time: 95144.053 ms — David Charles, Apr 20 '20 at 23:00
Please add full output of EXPLAIN ANALYZE to your question: edit your question (comment section is too small for that). — pifor, Apr 21 '20 at 06:29
Hi Pifor. I was able to get it to work by using the inline select in the on clause: on t2.org_id = (select ... limit 1); however when I tried it with the function there also that didn't do it. Thanks for looking into this. — David Charles, Apr 22 '20 at 01:43

PostgreSQL doesn't prune partitions correctly if I replace a numeric literal in the where clause with a simple query that returns 1 row

1 Answers1