Re: BUG #12933: Custom prepared plan vs partitioning. - Mailing list pgsql-bugs

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

> maxim.boguk@gmail.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 replannin=
g
> every time.  For a cost delta of about 0.6 unit I would have to agree wit=
h
> 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
>

=E2=80=8BHi,

Yep I see your point there.
=E2=80=8BThere 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 >=
=3D
'2015-01-01' and login_date<'2015-02-01')) INHERITS (parent);
create table child_2015_02 (like parent including all, check (login_date >=
=3D
'2015-02-01' and login_date<'2015-03-01')) INHERITS (parent);
create table child_2015_03 (like parent including all, check (login_date >=
=3D
'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=3D$1 and login_date=3D$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=3D297.15..297.20 rows=3D1 width=3D0) (actual time=3D2.274..2=
.275
rows=3D1 loops=3D1)
   InitPlan 1 (returns $0)
     ->  Result  (cost=3D0.00..5943.00 rows=3D20 width=3D0) (actual
time=3D2.268..2.268 rows=3D1 loops=3D1)
           ->  Append  (cost=3D0.00..5943.00 rows=3D20 width=3D0) (actual
time=3D2.266..2.266 rows=3D1 loops=3D1)
                 ->  Seq Scan on parent  (cost=3D0.00..0.00 rows=3D1 width=
=3D0)
(actual time=3D0.003..0.003 rows=3D0 loops=3D1)
                       Filter: ((user_id =3D 50) AND (login_date =3D
'2015-03-28'::date))
                 ->  Seq Scan on child_2015_03 parent  (cost=3D0.00..5943.0=
0
rows=3D19 width=3D0) (actual time=3D2.258..2.258 rows=3D1 loops=3D1)
                       Filter: ((user_id =3D 50) AND (login_date =3D
'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=3D168.20..168.25 rows=3D1 width=3D0) (actual time=3D83.222..=
83.224
rows=3D1 loops=3D1)
   InitPlan 1 (returns $0)
     ->  Result  (cost=3D0.00..17829.00 rows=3D106 width=3D0) (actual
time=3D83.215..83.215 rows=3D1 loops=3D1)
           ->  Append  (cost=3D0.00..17829.00 rows=3D106 width=3D0) (actual
time=3D83.210..83.210 rows=3D1 loops=3D1)
                 ->  Seq Scan on parent  (cost=3D0.00..0.00 rows=3D1 width=
=3D0)
(actual time=3D0.002..0.002 rows=3D0 loops=3D1)
                       Filter: ((user_id =3D $1) AND (login_date =3D $2))
                 ->  Seq Scan on child_2015_01 parent  (cost=3D0.00..5943.0=
0
rows=3D35 width=3D0) (actual time=3D40.592..40.592 rows=3D0 loops=3D1)
                       Filter: ((user_id =3D $1) AND (login_date =3D $2))
                       Rows Removed by Filter: 100000
                 ->  Seq Scan on child_2015_02 parent  (cost=3D0.00..5943.0=
0
rows=3D35 width=3D0) (actual time=3D40.248..40.248 rows=3D0 loops=3D1)
                       Filter: ((user_id =3D $1) AND (login_date =3D $2))
                       Rows Removed by Filter: 100000
                 ->  Seq Scan on child_2015_03 parent  (cost=3D0.00..5943.0=
0
rows=3D35 width=3D0) (actual time=3D2.358..2.358 rows=3D1 loops=3D1)
                       Filter: ((user_id =3D $1) AND (login_date =3D $2))
                       Rows Removed by Filter: 6175
 Total runtime: 83.317 ms

=E2=80=8BIn 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.

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