Re: BUG #12933: Custom prepared plan vs partitioning.

On Wed, Apr 1, 2015 at 5:25 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> maxim(dot)boguk(at)gmail(dot)com writes:
> > 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.
>
> This behavior is expected; after half a dozen trials the plancache will
> decide it's not getting enough win from custom plans to justify replanning
> every time. For a cost delta of about 0.6 unit I would have to agree with
> it. You may have a real case where it's doing something wrong, but this
> toy example doesn't demonstrate a bug IMO.
>
> regards, tom lane
>

​Hi,

Yep I see your point there.
​There are more close to reality test case:

-- create sample tables
create table parent (user_id int not null, login_date date not null);
create table child_2015_01 (like parent including all, check (login_date >=
'2015-01-01' and login_date<'2015-02-01')) INHERITS (parent);
create table child_2015_02 (like parent including all, check (login_date >=
'2015-02-01' and login_date<'2015-03-01')) INHERITS (parent);
create table child_2015_03 (like parent including all, check (login_date >=
'2015-03-01' and login_date<'2015-04-01')) INHERITS (parent);

--populate data
insert into child_2015_01 select (random()*100)::integer as user_id,
'2015-01-01'::date+(random()*27)::integer*'1 day'::interval as login_date
from generate_series(1,100000);
insert into child_2015_02 select (random()*100)::integer as user_id,
'2015-02-01'::date+(random()*27)::integer*'1 day'::interval as login_date
from generate_series(1,100000);
insert into child_2015_03 select (random()*100)::integer as user_id,
'2015-03-01'::date+(random()*27)::integer*'1 day'::interval as login_date
from generate_series(1,100000);

--analyze
analyze parent; analyze child_2015_01; analyze child_2015_02; analyze
child_2015_03;

prepare test(int, date) as select exists (select user_id from parent where
user_id=$1 and login_date=$2);
explain analyze execute test(50, '2015-03-28');

first 5 times plan:
explain analyze execute test(50, '2015-03-28');
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------
Result (cost=297.15..297.20 rows=1 width=0) (actual time=2.274..2.275
rows=1 loops=1)
InitPlan 1 (returns $0)
-> Result (cost=0.00..5943.00 rows=20 width=0) (actual
time=2.268..2.268 rows=1 loops=1)
-> Append (cost=0.00..5943.00 rows=20 width=0) (actual
time=2.266..2.266 rows=1 loops=1)
-> Seq Scan on parent (cost=0.00..0.00 rows=1 width=0)
(actual time=0.003..0.003 rows=0 loops=1)
Filter: ((user_id = 50) AND (login_date =
'2015-03-28'::date))
-> Seq Scan on child_2015_03 parent (cost=0.00..5943.00
rows=19 width=0) (actual time=2.258..2.258 rows=1 loops=1)
Filter: ((user_id = 50) AND (login_date =
'2015-03-28'::date))
Rows Removed by Filter: 6175
Total runtime: 2.336 ms

6th and later:
explain analyze execute test(50, '2015-03-28');
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------
Result (cost=168.20..168.25 rows=1 width=0) (actual time=83.222..83.224
rows=1 loops=1)
InitPlan 1 (returns $0)
-> Result (cost=0.00..17829.00 rows=106 width=0) (actual
time=83.215..83.215 rows=1 loops=1)
-> Append (cost=0.00..17829.00 rows=106 width=0) (actual
time=83.210..83.210 rows=1 loops=1)
-> Seq Scan on parent (cost=0.00..0.00 rows=1 width=0)
(actual time=0.002..0.002 rows=0 loops=1)
Filter: ((user_id = $1) AND (login_date = $2))
-> Seq Scan on child_2015_01 parent (cost=0.00..5943.00
rows=35 width=0) (actual time=40.592..40.592 rows=0 loops=1)
Filter: ((user_id = $1) AND (login_date = $2))
Rows Removed by Filter: 100000
-> Seq Scan on child_2015_02 parent (cost=0.00..5943.00
rows=35 width=0) (actual time=40.248..40.248 rows=0 loops=1)
Filter: ((user_id = $1) AND (login_date = $2))
Rows Removed by Filter: 100000
-> Seq Scan on child_2015_03 parent (cost=0.00..5943.00
rows=35 width=0) (actual time=2.358..2.358 rows=1 loops=1)
Filter: ((user_id = $1) AND (login_date = $2))
Rows Removed by Filter: 6175
Total runtime: 83.317 ms

​In production case there had been 6 years worth archive with all
partitions except current month completely cold. And of course required
partition been located at the end of the partition list.

As I could see there are problem with cost estimation for such cases.

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