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/=
>