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(dot)boguk(at)gmail(dot)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/