BUG #12933: Custom prepared plan vs partitioning.

The following bug has been logged on the website:

Bug reference:      12933
Logged by:          Maksym Boguk
Email address:      maxim.boguk@gmail.com
PostgreSQL version: 9.2.10
Operating system:   Linux
Description:

Hi,

I found case when custom plans with partitioning shows strange behavior.
First 5 repetitions of execute with the same parameters I getting fast
custom plan, on 6th run plan switch to slow generic (all-partitions)
version.

Very simplified test case:

create table parent (id serial);
create table child1 (like parent including all, check (id between 1 and 10))
INHERITS (parent);
create table child2 (like parent including all, check (id between 11 and
20)) INHERITS (parent);
create table child3 (like parent including all, check (id between 21 and
30)) INHERITS (parent);


prepare test(integer) as select * from parent where id=$1 limit 1;
explain execute test(5);
repeat explain 6 times.

first 5 time correct custom plan:
                                    QUERY PLAN
----------------------------------------------------------------------------------
 Limit  (cost=0.00..10.46 rows=1 width=4)
   ->  Result  (cost=0.00..136.00 rows=13 width=4)
         ->  Append  (cost=0.00..136.00 rows=13 width=4)
               ->  Seq Scan on parent  (cost=0.00..0.00 rows=1 width=4)
                     Filter: (id = 5)
               ->  Seq Scan on child1 parent  (cost=0.00..136.00 rows=12
width=4)
                     Filter: (id = 5)

6th and all after - slower generic plan
                                    QUERY PLAN
----------------------------------------------------------------------------------
 Limit  (cost=0.00..11.03 rows=1 width=4)
   ->  Result  (cost=0.00..408.00 rows=37 width=4)
         ->  Append  (cost=0.00..408.00 rows=37 width=4)
               ->  Seq Scan on parent  (cost=0.00..0.00 rows=1 width=4)
                     Filter: (id = $1)
               ->  Seq Scan on child1 parent  (cost=0.00..136.00 rows=12
width=4)
                     Filter: (id = $1)
               ->  Seq Scan on child2 parent  (cost=0.00..136.00 rows=12
width=4)
                     Filter: (id = $1)
               ->  Seq Scan on child3 parent  (cost=0.00..136.00 rows=12
width=4)
                     Filter: (id = $1)

Without LIMIT there no such issues happen but with LIMIT it very repeatable
over large range of partitioning structures/query conditions tested.

9.4.1 produce the same behavior.

--
Maxim Boguk
Senior Postgresql DBA
http://www.postgresql-consulting.ru/