pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedpostgresql 10.1 wrong plan in when using partitions bug
19+ messages / 7 participants
[nested] [flat]
* postgresql 10.1 wrong plan in when using partitions bug
@ 2018-02-04 10:14 Mariel Cherkassky <mariel.cherkassky@gmail.com>
0 siblings, 2 replies; 19+ messages in thread
From: Mariel Cherkassky @ 2018-02-04 10:14 UTC (permalink / raw)
To: pgsql-performance
Hi,
I configured range partitions on a date column of my main table(log_full).
Each partition represents a day in the month. Every day partition has a
list parition of 4 tables on a text column.
log_full
log_full_01_11_2017 -->
log_full _01_11_2017_x1
log_full _01_11_2017_x2
log_full _01_11_2017_x3
log_full _01_11_2017_x4
log_full_02_11_2017
log_full _02_11_2017_x1
log_full _02_11_2017_x2
log_full _02_11_2017_x3
log_full _02_11_2017_x4
and so on....
The date column consist of date in the next format : YYYY-MM-DD HH:24:SS
for example : 2017-11-01 00:01:40
I wanted to check the plan that I'm getting for a query that is using the
date column and it seems that the planner choose to do seq scans on all
tables.
-Each partition consist from 15M rows.
I have about 120 partitions.
The query :
explain select count(*) from log_full where end_date between
to_date('2017/12/03','YY/MM/DD') and to_date('2017/12/03','YY/MM/DD');
The output is too long but it do full scans on all paritions...
any idea what can be the problem? Is it connected to the date format ?
Thanks , Mariel.
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: postgresql 10.1 wrong plan in when using partitions bug
@ 2018-02-04 12:03 Tomas Vondra <tomas.vondra@2ndquadrant.com>
parent: Mariel Cherkassky <mariel.cherkassky@gmail.com>
1 sibling, 1 reply; 19+ messages in thread
From: Tomas Vondra @ 2018-02-04 12:03 UTC (permalink / raw)
To: Mariel Cherkassky <mariel.cherkassky@gmail.com>; pgsql-performance
On 02/04/2018 11:14 AM, Mariel Cherkassky wrote:
>
> Hi,
> I configured range partitions on a date column of my main
> table(log_full). Each partition represents a day in the month. Every day
> partition has a list parition of 4 tables on a text column.
>
> log_full
> log_full_01_11_2017 -->
> log_full _01_11_2017_x1
> log_full _01_11_2017_x2
> log_full _01_11_2017_x3
> log_full _01_11_2017_x4
> log_full_02_11_2017
> log_full _02_11_2017_x1
> log_full _02_11_2017_x2
> log_full _02_11_2017_x3
> log_full _02_11_2017_x4
>
> and so on....
>
>
> The date column consist of date in the next format : YYYY-MM-DD HH:24:SS
> for example : 2017-11-01 00:01:40
>
> I wanted to check the plan that I'm getting for a query that is using
> the date column and it seems that the planner choose to do seq scans on
> all tables.
>
> -Each partition consist from 15M rows.
> I have about 120 partitions.
>
> The query :
> explain select count(*) from log_full where end_date between
> to_date('2017/12/03','YY/MM/DD') and to_date('2017/12/03','YY/MM/DD');
>
> The output is too long but it do full scans on all paritions...
> any idea what can be the problem? Is it connected to the date format ?
>
You haven't shown us how the partitions are defined, nor the query plan.
So it's rather hard to say. You mentioned text format, but then you use
to_date() to query the partitioned table. Which I guess might be the
cause, but it's hard to say for sure.
regards
--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: postgresql 10.1 wrong plan in when using partitions bug
@ 2018-02-04 12:15 Rick Otten <rottenwindfish@gmail.com>
parent: Mariel Cherkassky <mariel.cherkassky@gmail.com>
1 sibling, 1 reply; 19+ messages in thread
From: Rick Otten @ 2018-02-04 12:15 UTC (permalink / raw)
To: Mariel Cherkassky <mariel.cherkassky@gmail.com>; pgsql-performance
On Sun, Feb 4, 2018 at 5:14 AM, Mariel Cherkassky <
mariel.cherkassky@gmail.com> wrote:
>
> Hi,
> I configured range partitions on a date column of my main table(log_full).
> Each partition represents a day in the month. Every day partition has a
> list parition of 4 tables on a text column.
>
> log_full
> log_full_01_11_2017 -->
> log_full _01_11_2017_x1
> log_full _01_11_2017_x2
> log_full _01_11_2017_x3
> log_full _01_11_2017_x4
> log_full_02_11_2017
> log_full _02_11_2017_x1
> log_full _02_11_2017_x2
> log_full _02_11_2017_x3
> log_full _02_11_2017_x4
>
> and so on....
>
>
> The date column consist of date in the next format : YYYY-MM-DD HH:24:SS
> for example : 2017-11-01 00:01:40
>
> I wanted to check the plan that I'm getting for a query that is using the
> date column and it seems that the planner choose to do seq scans on all
> tables.
>
> -Each partition consist from 15M rows.
> I have about 120 partitions.
>
> The query :
> explain select count(*) from log_full where end_date between
> to_date('2017/12/03','YY/MM/DD') and to_date('2017/12/03','YY/MM/DD');
>
> The output is too long but it do full scans on all paritions...
> any idea what can be the problem? Is it connected to the date format ?
>
> Thanks , Mariel.
>
I'm wrestling with a very similar problem too - except instead of official
partitions I have a views on top of a bunch (50+) of unioned materialized
views, each "partition" with 10M - 100M rows. On 9.6.6 the queries would
use the indexes on each materialized view. On 10.1, every materialized
view is sequence scanned. (Killing the performance of many queries.) I
have 4 or 5 sets of materialized views organized this way with views on top
of them.
I've checked for invalid indexes.
I've done Analyze, and Vaccuum Analyze on all sub-materialized views.
I've reindexed the materialized views.
I've experimented with geqo tunables.
I've experimented with turning parallel gather off and on and setting it
to different levels.
I've tried setting random page cost very high, and very low.
I tried turning nested loops on and off.
I tried setting effective_cache_size very small.
None of the various queries using these views on top of my hand constructed
"partitions" are using indexes.
All of the exact same queries used the indexes in 9.6.6 before the
upgrade. Without the indexes, hitting these 1B+ row aggregate tables I'm
seeing a 10x to 100x slowdown since upgrading. This is killing us.
Not only that but with 50 tables under the view, and each one getting a
parallel sequence scan, it is kind of impressive how much CPU one of these
queries can use at once.
I'm mostly hoping with fingers crossed that something in 10.2, which is
coming out next week, fixes it. I was planning on posting my dilemma to
this list this morning since I'm running out of ideas. I really need to
fix the issue this weekend to meet some business deadlines for data
processing early in the week. So my other hail mary pass this weekend,
besides seeking ideas on this list, was to see if I could bump my version
to 10.2 early. (I'm not sure how to do that since I've been using Ubuntu
packages and waiting for official releases prior to now, but I'm sure I can
figure it out.)
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: postgresql 10.1 wrong plan in when using partitions bug
@ 2018-02-04 12:19 Mariel Cherkassky <mariel.cherkassky@gmail.com>
parent: Tomas Vondra <tomas.vondra@2ndquadrant.com>
0 siblings, 2 replies; 19+ messages in thread
From: Mariel Cherkassky @ 2018-02-04 12:19 UTC (permalink / raw)
To: Tomas Vondra <tomas.vondra@2ndquadrant.com>; +Cc: pgsql-performance
Mybe I wasnt clear. I'm having a 2 layers patitions mechanism :
My main table is called log_full :
CREATE TABLE log_full (a text,b text,c text, start_stop text, end_Date
date) partition range by (end_date))
Every day I create a partition that represent data from that day :
create table log_full_04_02_2018 partition of radius_log_full(end_date) for
VALUES from ('04-02-2018 00:00:00') TO ('05-02-2018 00:00:00') partition by
list (start_stop) ;
The partition that represent the current day consist of 8 paritions on
column start_stop that look like that :
create table log_full_04_02_2018_action_status partition of
log_full_04_02_2018 for VALUES in ('Start','Stop');
ALTER TABLE ONLY log_full_04_02_2018_action_status
ADD CONSTRAINT log_full_04_02_2018_action_status_pkey PRIMARY KEY (a,
b, c);
I checked the plan of the next query :
explain select count(*) from log_full where end_date between
to_date('2017/12/03','YY/MM/DD') and to_date('2017/12/03','YY/MM/DD');
and the result if full scan on all partitions.
Why it decided to run a full table scan on all partitions ?
2018-02-04 14:03 GMT+02:00 Tomas Vondra <tomas.vondra@2ndquadrant.com>:
>
>
> On 02/04/2018 11:14 AM, Mariel Cherkassky wrote:
> >
> > Hi,
> > I configured range partitions on a date column of my main
> > table(log_full). Each partition represents a day in the month. Every day
> > partition has a list parition of 4 tables on a text column.
> >
> > log_full
> > log_full_01_11_2017 -->
> > log_full _01_11_2017_x1
> > log_full _01_11_2017_x2
> > log_full _01_11_2017_x3
> > log_full _01_11_2017_x4
> > log_full_02_11_2017
> > log_full _02_11_2017_x1
> > log_full _02_11_2017_x2
> > log_full _02_11_2017_x3
> > log_full _02_11_2017_x4
> >
> > and so on....
> >
> >
> > The date column consist of date in the next format : YYYY-MM-DD HH:24:SS
> > for example : 2017-11-01 00:01:40
> >
> > I wanted to check the plan that I'm getting for a query that is using
> > the date column and it seems that the planner choose to do seq scans on
> > all tables.
> >
> > -Each partition consist from 15M rows.
> > I have about 120 partitions.
> >
> > The query :
> > explain select count(*) from log_full where end_date between
> > to_date('2017/12/03','YY/MM/DD') and to_date('2017/12/03','YY/MM/DD');
> >
> > The output is too long but it do full scans on all paritions...
> > any idea what can be the problem? Is it connected to the date format ?
> >
>
> You haven't shown us how the partitions are defined, nor the query plan.
> So it's rather hard to say. You mentioned text format, but then you use
> to_date() to query the partitioned table. Which I guess might be the
> cause, but it's hard to say for sure.
>
> regards
>
> --
> Tomas Vondra http://www.2ndQuadrant.com
> PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
>
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: postgresql 10.1 wrong plan in when using partitions bug
@ 2018-02-04 13:19 legrand legrand <legrand_legrand@hotmail.com>
parent: Mariel Cherkassky <mariel.cherkassky@gmail.com>
1 sibling, 2 replies; 19+ messages in thread
From: legrand legrand @ 2018-02-04 13:19 UTC (permalink / raw)
To: pgsql-performance
What is the value of guc constrain_exclusion ?
--
Sent from: http://www.postgresql-archive.org/PostgreSQL-performance-f2050081.html
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: postgresql 10.1 wrong plan in when using partitions bug
@ 2018-02-04 13:22 Mariel Cherkassky <mariel.cherkassky@gmail.com>
parent: legrand legrand <legrand_legrand@hotmail.com>
1 sibling, 1 reply; 19+ messages in thread
From: Mariel Cherkassky @ 2018-02-04 13:22 UTC (permalink / raw)
To: legrand legrand <legrand_legrand@hotmail.com>; +Cc: pgsql-performance
show constraint_exclusion;
constraint_exclusion
----------------------
partition
(1 row)
2018-02-04 15:19 GMT+02:00 legrand legrand <legrand_legrand@hotmail.com>:
> What is the value of guc constrain_exclusion ?
>
>
>
> --
> Sent from: http://www.postgresql-archive.org/PostgreSQL-performance-
> f2050081.html
>
>
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: postgresql 10.1 wrong plan in when using partitions bug
@ 2018-02-04 13:29 legrand legrand <legrand_legrand@hotmail.com>
parent: Mariel Cherkassky <mariel.cherkassky@gmail.com>
0 siblings, 1 reply; 19+ messages in thread
From: legrand legrand @ 2018-02-04 13:29 UTC (permalink / raw)
To: pgsql-performance
Explain analyse
Output ?
--
Sent from: http://www.postgresql-archive.org/PostgreSQL-performance-f2050081.html
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: postgresql 10.1 wrong plan in when using partitions bug
@ 2018-02-04 13:38 Rick Otten <rottenwindfish@gmail.com>
parent: legrand legrand <legrand_legrand@hotmail.com>
1 sibling, 0 replies; 19+ messages in thread
From: Rick Otten @ 2018-02-04 13:38 UTC (permalink / raw)
To: legrand legrand <legrand_legrand@hotmail.com>; +Cc: pgsql-performance
On Sun, Feb 4, 2018 at 8:19 AM, legrand legrand <legrand_legrand@hotmail.com
> wrote:
> What is the value of guc constrain_exclusion ?
>
>
>
In my use case, which is a big union all behind a view, setting this to
off, on, or partition makes no difference. It still sequence scans all of
the sub-tables in pg 10.1 whereas it used the indexes in 9.6.
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: postgresql 10.1 wrong plan in when using partitions bug
@ 2018-02-04 13:43 Mariel Cherkassky <mariel.cherkassky@gmail.com>
parent: legrand legrand <legrand_legrand@hotmail.com>
0 siblings, 1 reply; 19+ messages in thread
From: Mariel Cherkassky @ 2018-02-04 13:43 UTC (permalink / raw)
To: legrand legrand <legrand_legrand@hotmail.com>; +Cc: pgsql-performance
explain analyze takes too much time.. hours ...
I run it now but it will take some time.
The output of the explain :
Finalize Aggregate (cost=38058211.38..38058211.39 rows=1 width=8)
-> Gather (cost=38058211.16..38058211.37 rows=2 width=8)
Workers Planned: 2
-> Partial Aggregate (cost=38057211.16..38057211.17 rows=1
width=8)
-> Append (cost=0.00..38040836.26 rows=6549963 width=0)
-> Parallel Seq Scan on
log_full_1_11_2017_action_status (cost=0.00..39863.21 rows=1 width=
0)
Filter: ((end_date >=
to_date('2017/12/03'::text, 'YY/MM/DD'::text)) AND (end_date <= to_date(
'2017/12/04'::text, 'YY/MM/DD'::text)))
-> Parallel Seq Scan on
log_full_1_11_2017_alive_status (cost=0.00..702893.03 rows=1 width=
0)
Filter: ((end_date >=
to_date('2017/12/03'::text, 'YY/MM/DD'::text)) AND (end_date <= to_date(
'2017/12/04'::text, 'YY/MM/DD'::text)))
-> Parallel Seq Scan on
log_full_1_11_2017_modem_status (cost=0.00..10.59 rows=1 width=0)
Filter: ((end_date >=
to_date('2017/12/03'::text, 'YY/MM/DD'::text)) AND (end_date <= to_date(
'2017/12/04'::text, 'YY/MM/DD'::text)))
and so on parallel seq for each partition that I have..
2018-02-04 15:29 GMT+02:00 legrand legrand <legrand_legrand@hotmail.com>:
> Explain analyse
> Output ?
>
>
>
> --
> Sent from: http://www.postgresql-archive.org/PostgreSQL-performance-
> f2050081.html
>
>
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: postgresql 10.1 wrong plan in when using partitions bug
@ 2018-02-04 14:41 Mariel Cherkassky <mariel.cherkassky@gmail.com>
parent: Mariel Cherkassky <mariel.cherkassky@gmail.com>
0 siblings, 0 replies; 19+ messages in thread
From: Mariel Cherkassky @ 2018-02-04 14:41 UTC (permalink / raw)
To: legrand legrand <legrand_legrand@hotmail.com>; +Cc: pgsql-performance
Output of explain analyze :
explain analyze select count(*) from log_full where end_date between
to_date('2017/12/03','YY/MM/DD') and to_date('2017/12/04','YY/MM/DD');
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------
Finalize Aggregate (cost=38058211.38..38058211.39 rows=1 width=8) (actual
time=3502304.726..3502304.726 rows=1 loops=1)
-> Gather (cost=38058211.16..38058211.37 rows=2 width=8) (actual
time=3502179.810..3502251.520 rows=3 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Partial Aggregate (cost=38057211.16..38057211.17 rows=1
width=8) (actual time=3500338.084..3500338.084 rows
=1 loops=3)
-> Append (cost=0.00..38040836.26 rows=6549963 width=0)
(actual time=1513398.593..3499538.302 rows=52402
29 loops=3)
-> Parallel Seq Scan on
log_full_1_11_2017_action_status (cost=0.00..39863.21 rows=1 width=
0) (actual time=4047.915..4047.915 rows=0 loops=3)
Filter: ((end_date >=
to_date('2017/12/03'::text, 'YY/MM/DD'::text)) AND (end_date <= to_date(
'2017/12/04'::text, 'YY/MM/DD'::text)))
Rows Removed by Filter: 286924
-> Parallel Seq Scan on
log_full_1_11_2017_alive_status (cost=0.00..702893.03 rows=1 width=
0) (actual time=63648.476..63648.476 rows=0 loops=3)
Filter: ((end_date >=
to_date('2017/12/03'::text, 'YY/MM/DD'::text)) AND (end_date <= to_date(
'2017/12/04'::text, 'YY/MM/DD'::text)))
Rows Removed by Filter: 4955092
-> Parallel Seq Scan on
log_full_1_11_2017_modem_status (cost=0.00..10.59 rows=1 width=0) (
actual time=0.001..0.001 rows=0 loops=3)
Filter: ((end_date >=
to_date('2017/12/03'::text, 'YY/MM/DD'::text)) AND (end_date <= to_date(
'2017/12/04'::text, 'YY/MM/DD'::text)))
....................
and so on full on on partitions..
2018-02-04 15:43 GMT+02:00 Mariel Cherkassky <mariel.cherkassky@gmail.com>:
> explain analyze takes too much time.. hours ...
> I run it now but it will take some time.
> The output of the explain :
>
> Finalize Aggregate (cost=38058211.38..38058211.39 rows=1 width=8)
> -> Gather (cost=38058211.16..38058211.37 rows=2 width=8)
> Workers Planned: 2
> -> Partial Aggregate (cost=38057211.16..38057211.17 rows=1
> width=8)
> -> Append (cost=0.00..38040836.26 rows=6549963 width=0)
> -> Parallel Seq Scan on log_full_1_11_2017_action_status
> (cost=0.00..39863.21 rows=1 width=
> 0)
> Filter: ((end_date >=
> to_date('2017/12/03'::text, 'YY/MM/DD'::text)) AND (end_date <= to_date(
> '2017/12/04'::text, 'YY/MM/DD'::text)))
> -> Parallel Seq Scan on log_full_1_11_2017_alive_status
> (cost=0.00..702893.03 rows=1 width=
> 0)
> Filter: ((end_date >=
> to_date('2017/12/03'::text, 'YY/MM/DD'::text)) AND (end_date <= to_date(
> '2017/12/04'::text, 'YY/MM/DD'::text)))
> -> Parallel Seq Scan on log_full_1_11_2017_modem_status
> (cost=0.00..10.59 rows=1 width=0)
> Filter: ((end_date >=
> to_date('2017/12/03'::text, 'YY/MM/DD'::text)) AND (end_date <= to_date(
> '2017/12/04'::text, 'YY/MM/DD'::text)))
>
> and so on parallel seq for each partition that I have..
>
>
> 2018-02-04 15:29 GMT+02:00 legrand legrand <legrand_legrand@hotmail.com>:
>
>> Explain analyse
>> Output ?
>>
>>
>>
>> --
>> Sent from: http://www.postgresql-archive.org/PostgreSQL-performance-f20
>> 50081.html
>>
>>
>
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: postgresql 10.1 wrong plan in when using partitions bug
@ 2018-02-04 14:54 Andreas Kretschmer <andreas@a-kretschmer.de>
parent: Mariel Cherkassky <mariel.cherkassky@gmail.com>
1 sibling, 1 reply; 19+ messages in thread
From: Andreas Kretschmer @ 2018-02-04 14:54 UTC (permalink / raw)
To: pgsql-performance@lists.postgresql.org
Am 04.02.2018 um 13:19 schrieb Mariel Cherkassky:
> I checked the plan of the next query :
> explain select count(*) from log_full where end_date between
> to_date('2017/12/03','YY/MM/DD') and to_date('2017/12/03','YY/MM/DD');
>
can you rewrite the query to
... where end_date between '2017/12/03' and '2017/12/03'
simple test-case:
test=*# \d+ t
Table "public.t"
Column | Type | Collation | Nullable | Default | Storage | Stats
target | Description
--------+------+-----------+----------+---------+---------+--------------+-------------
d | date | | | | plain | |
Partition key: RANGE (d)
Partitions: t_01 FOR VALUES FROM ('2018-02-04') TO ('2018-02-05'),
t_02 FOR VALUES FROM ('2018-02-05') TO ('2018-02-06')
test=*# explain analyse select * from t where d between
to_date('2018/02/04','YY/MM/DD') and to_date('2018/02/04','YY/MM/DD');
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------
Append (cost=0.00..122.00 rows=26 width=4) (actual time=0.006..0.006
rows=0 loops=1)
-> Seq Scan on t_01 (cost=0.00..61.00 rows=13 width=4) (actual
time=0.004..0.004 rows=0 loops=1)
Filter: ((d >= to_date('2018/02/04'::text, 'YY/MM/DD'::text))
AND (d <= to_date('2018/02/04'::text, 'YY/MM/DD'::text)))
-> Seq Scan on t_02 (cost=0.00..61.00 rows=13 width=4) (actual
time=0.001..0.001 rows=0 loops=1)
Filter: ((d >= to_date('2018/02/04'::text, 'YY/MM/DD'::text))
AND (d <= to_date('2018/02/04'::text, 'YY/MM/DD'::text)))
Planning time: 0.241 ms
Execution time: 0.042 ms
(7 rows)
test=*# explain analyse select * from t where d between '2018/02/04' and
'2018/02/04';
QUERY PLAN
------------------------------------------------------------------------------------------------------
Append (cost=0.00..48.25 rows=13 width=4) (actual time=0.005..0.005
rows=0 loops=1)
-> Seq Scan on t_01 (cost=0.00..48.25 rows=13 width=4) (actual
time=0.004..0.004 rows=0 loops=1)
Filter: ((d >= '2018-02-04'::date) AND (d <= '2018-02-04'::date))
Planning time: 0.203 ms
Execution time: 0.030 ms
(5 rows)
test=*#
maybe the planner should be smart enough to do that for you, but
obvously he can't. So it's a workaround, but it seems to solve the problem.
Regards, Andreas
--
2ndQuadrant - The PostgreSQL Support Company.
www.2ndQuadrant.com
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: postgresql 10.1 wrong plan in when using partitions bug
@ 2018-02-04 15:06 Mariel Cherkassky <mariel.cherkassky@gmail.com>
parent: Andreas Kretschmer <andreas@a-kretschmer.de>
0 siblings, 2 replies; 19+ messages in thread
From: Mariel Cherkassky @ 2018-02-04 15:06 UTC (permalink / raw)
To: Andreas Kretschmer <andreas@a-kretschmer.de>; +Cc: pgsql-performance@lists.postgresql.org
Great, it solved the issue. Seems problematic that the planner do full
scans on all partitions in the first case isnt it ? Seems like a bug ?
2018-02-04 16:54 GMT+02:00 Andreas Kretschmer <andreas@a-kretschmer.de>:
>
>
> Am 04.02.2018 um 13:19 schrieb Mariel Cherkassky:
>
>> I checked the plan of the next query :
>> explain select count(*) from log_full where end_date between
>> to_date('2017/12/03','YY/MM/DD') and to_date('2017/12/03','YY/MM/DD');
>>
>>
> can you rewrite the query to
>
> ... where end_date between '2017/12/03' and '2017/12/03'
>
>
>
> simple test-case:
>
> test=*# \d+ t
> Table "public.t"
> Column | Type | Collation | Nullable | Default | Storage | Stats target |
> Description
> --------+------+-----------+----------+---------+---------+-
> -------------+-------------
> d | date | | | | plain | |
> Partition key: RANGE (d)
> Partitions: t_01 FOR VALUES FROM ('2018-02-04') TO ('2018-02-05'),
> t_02 FOR VALUES FROM ('2018-02-05') TO ('2018-02-06')
>
> test=*# explain analyse select * from t where d between
> to_date('2018/02/04','YY/MM/DD') and to_date('2018/02/04','YY/MM/DD');
> QUERY PLAN
> ------------------------------------------------------------
> ---------------------------------------------------------------------
> Append (cost=0.00..122.00 rows=26 width=4) (actual time=0.006..0.006
> rows=0 loops=1)
> -> Seq Scan on t_01 (cost=0.00..61.00 rows=13 width=4) (actual
> time=0.004..0.004 rows=0 loops=1)
> Filter: ((d >= to_date('2018/02/04'::text, 'YY/MM/DD'::text)) AND
> (d <= to_date('2018/02/04'::text, 'YY/MM/DD'::text)))
> -> Seq Scan on t_02 (cost=0.00..61.00 rows=13 width=4) (actual
> time=0.001..0.001 rows=0 loops=1)
> Filter: ((d >= to_date('2018/02/04'::text, 'YY/MM/DD'::text)) AND
> (d <= to_date('2018/02/04'::text, 'YY/MM/DD'::text)))
> Planning time: 0.241 ms
> Execution time: 0.042 ms
> (7 rows)
>
> test=*# explain analyse select * from t where d between '2018/02/04' and
> '2018/02/04';
> QUERY PLAN
> ------------------------------------------------------------
> ------------------------------------------
> Append (cost=0.00..48.25 rows=13 width=4) (actual time=0.005..0.005
> rows=0 loops=1)
> -> Seq Scan on t_01 (cost=0.00..48.25 rows=13 width=4) (actual
> time=0.004..0.004 rows=0 loops=1)
> Filter: ((d >= '2018-02-04'::date) AND (d <= '2018-02-04'::date))
> Planning time: 0.203 ms
> Execution time: 0.030 ms
> (5 rows)
>
> test=*#
>
> maybe the planner should be smart enough to do that for you, but obvously
> he can't. So it's a workaround, but it seems to solve the problem.
>
>
> Regards, Andreas
>
> --
> 2ndQuadrant - The PostgreSQL Support Company.
> www.2ndQuadrant.com
>
>
>
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: postgresql 10.1 wrong plan in when using partitions bug
@ 2018-02-04 15:25 Justin Pryzby <pryzby@telsasoft.com>
parent: Mariel Cherkassky <mariel.cherkassky@gmail.com>
1 sibling, 1 reply; 19+ messages in thread
From: Justin Pryzby @ 2018-02-04 15:25 UTC (permalink / raw)
To: Mariel Cherkassky <mariel.cherkassky@gmail.com>; +Cc: Andreas Kretschmer <andreas@a-kretschmer.de>; pgsql-performance@lists.postgresql.org
On Sun, Feb 04, 2018 at 05:06:38PM +0200, Mariel Cherkassky wrote:
> Great, it solved the issue. Seems problematic that the planner do full
> scans on all partitions in the first case isnt it ? Seems like a bug ?
See also:
https://www.postgresql.org/message-id/20170725131650.GA30519%40telsasoft.com
https://www.postgresql.org/message-id/20170825154434.GC16287%40telsasoft.com
Justin
2018-02-04 16:54 GMT+02:00 Andreas Kretschmer <andreas@a-kretschmer.de>:
>
> >
> >
Am 04.02.2018 um 13:19 schrieb Mariel Cherkassky:
> >
> >> I checked the plan of the next query :
> >> explain select count(*) from log_full where end_date between
> >> to_date('2017/12/03','YY/MM/DD') and to_date('2017/12/03','YY/MM/DD');
> >>
> >>
> > can you rewrite the query to
> >
> > ... where end_date between '2017/12/03' and '2017/12/03'
> >
> > maybe the planner should be smart enough to do that for you, but obvously
> > he can't. So it's a workaround, but it seems to solve the problem.
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: postgresql 10.1 wrong plan in when using partitions bug
@ 2018-02-04 15:28 Mariel Cherkassky <mariel.cherkassky@gmail.com>
parent: Justin Pryzby <pryzby@telsasoft.com>
0 siblings, 1 reply; 19+ messages in thread
From: Mariel Cherkassky @ 2018-02-04 15:28 UTC (permalink / raw)
To: Justin Pryzby <pryzby@telsasoft.com>; +Cc: Andreas Kretschmer <andreas@a-kretschmer.de>; pgsql-performance@lists.postgresql.org
I read those two links and I dont think that they are relevant because : 1
1)I didnt do any join.
2)I used a where clause in my select
2018-02-04 17:25 GMT+02:00 Justin Pryzby <pryzby@telsasoft.com>:
> On Sun, Feb 04, 2018 at 05:06:38PM +0200, Mariel Cherkassky wrote:
> > Great, it solved the issue. Seems problematic that the planner do full
> > scans on all partitions in the first case isnt it ? Seems like a bug ?
>
> See also:
> https://www.postgresql.org/message-id/20170725131650.
> GA30519%40telsasoft.com
> https://www.postgresql.org/message-id/20170825154434.
> GC16287%40telsasoft.com
>
> Justin
>
> 2018-02-04 16:54 GMT+02:00 Andreas Kretschmer <andreas@a-kretschmer.de>:
> >
> > >
> > >
> Am 04.02.2018 um 13:19 schrieb Mariel Cherkassky:
> > >
> > >> I checked the plan of the next query :
> > >> explain select count(*) from log_full where end_date between
> > >> to_date('2017/12/03','YY/MM/DD') and to_date('2017/12/03','YY/MM/
> DD');
> > >>
> > >>
> > > can you rewrite the query to
> > >
> > > ... where end_date between '2017/12/03' and '2017/12/03'
> > >
> > > maybe the planner should be smart enough to do that for you, but
> obvously
> > > he can't. So it's a workaround, but it seems to solve the problem.
>
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: postgresql 10.1 wrong plan in when using partitions bug
@ 2018-02-04 15:35 Tom Lane <tgl@sss.pgh.pa.us>
parent: Rick Otten <rottenwindfish@gmail.com>
0 siblings, 1 reply; 19+ messages in thread
From: Tom Lane @ 2018-02-04 15:35 UTC (permalink / raw)
To: Rick Otten <rottenwindfish@gmail.com>; +Cc: Mariel Cherkassky <mariel.cherkassky@gmail.com>; pgsql-performance
Rick Otten <rottenwindfish@gmail.com> writes:
> I'm wrestling with a very similar problem too - except instead of official
> partitions I have a views on top of a bunch (50+) of unioned materialized
> views, each "partition" with 10M - 100M rows. On 9.6.6 the queries would
> use the indexes on each materialized view. On 10.1, every materialized
> view is sequence scanned.
Can you post a self-contained example of this behavior? My gut reaction
is that the changes for the partitioning feature broke some optimization
that used to work ... but it could easily be something else, too. Hard
to say with nothing concrete to look at.
> I'm mostly hoping with fingers crossed that something in 10.2, which is
> coming out next week, fixes it.
If you'd reported this in suitable detail awhile ago, we might have been
able to fix it for 10.2. At this point, with barely 30 hours remaining
before the planned release wrap, it's unlikely that anything but the most
trivial fixes could get done in time.
regards, tom lane
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: postgresql 10.1 wrong plan in when using partitions bug
@ 2018-02-04 15:39 Tom Lane <tgl@sss.pgh.pa.us>
parent: Mariel Cherkassky <mariel.cherkassky@gmail.com>
1 sibling, 1 reply; 19+ messages in thread
From: Tom Lane @ 2018-02-04 15:39 UTC (permalink / raw)
To: Mariel Cherkassky <mariel.cherkassky@gmail.com>; +Cc: Andreas Kretschmer <andreas@a-kretschmer.de>; pgsql-performance@lists.postgresql.org
Mariel Cherkassky <mariel.cherkassky@gmail.com> writes:
> Great, it solved the issue. Seems problematic that the planner do full
> scans on all partitions in the first case isnt it ? Seems like a bug ?
to_date isn't an immutable function (it depends on timezone and possibly
some other GUC settings). So there's a limited amount that the planner
can do with it.
regards, tom lane
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: postgresql 10.1 wrong plan in when using partitions bug
@ 2018-02-04 15:42 Mariel Cherkassky <mariel.cherkassky@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 19+ messages in thread
From: Mariel Cherkassky @ 2018-02-04 15:42 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Andreas Kretschmer <andreas@a-kretschmer.de>; pgsql-performance@lists.postgresql.org
Hi Tom,
Did you hear about any solution that is similar to oracle`s global index ?
Is there any way to query all the partitions with one index?
2018-02-04 17:39 GMT+02:00 Tom Lane <tgl@sss.pgh.pa.us>:
> Mariel Cherkassky <mariel.cherkassky@gmail.com> writes:
> > Great, it solved the issue. Seems problematic that the planner do full
> > scans on all partitions in the first case isnt it ? Seems like a bug ?
>
> to_date isn't an immutable function (it depends on timezone and possibly
> some other GUC settings). So there's a limited amount that the planner
> can do with it.
>
> regards, tom lane
>
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: postgresql 10.1 wrong plan in when using partitions bug
@ 2018-02-04 16:04 Rick Otten <rottenwindfish@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 19+ messages in thread
From: Rick Otten @ 2018-02-04 16:04 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Mariel Cherkassky <mariel.cherkassky@gmail.com>; pgsql-performance
On Sun, Feb 4, 2018 at 10:35 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Rick Otten <rottenwindfish@gmail.com> writes:
> > I'm wrestling with a very similar problem too - except instead of
> official
> > partitions I have a views on top of a bunch (50+) of unioned materialized
> > views, each "partition" with 10M - 100M rows. On 9.6.6 the queries would
> > use the indexes on each materialized view. On 10.1, every materialized
> > view is sequence scanned.
>
> Can you post a self-contained example of this behavior? My gut reaction
> is that the changes for the partitioning feature broke some optimization
> that used to work ... but it could easily be something else, too. Hard
> to say with nothing concrete to look at.
>
>
I think it is worth trying to reproduce in an example. I'll try to cook
something up that illustrates it. It should be doable.
> > I'm mostly hoping with fingers crossed that something in 10.2, which is
> > coming out next week, fixes it.
>
> If you'd reported this in suitable detail awhile ago, we might have been
> able to fix it for 10.2. At this point, with barely 30 hours remaining
> before the planned release wrap, it's unlikely that anything but the most
> trivial fixes could get done in time.
>
>
I wish I could move faster on identifying and reporting this sort of thing.
We only cut over to 10.1 about 2 weeks ago and didn't discover the issue
until we'd been running for a few days (and eliminated everything else we
could think of - including the bug that is fixed in 10.2 that crashes some
queries when they have parallel gather enabled).
My hope is that 10.2 will fix our issue "by accident" rather than on
purpose.
I'll try to build a test case this afternoon.
--
I use a view on top of the materialized views so I can swap them in and out
with a "create or replace" that doesn't disrupt downstream depndencies.
I'm currently thinking to work around this issue for the short term, I need
to build a mat view on top of the mat views, and then put my view on top of
that (so I can swap out the big matview without disrupting downstream
dependencies). It means a lot more disk will be needed, and moving
partitions around will be much less elegant, but I can live with that if it
fixes the performance problems caused by the sequence scanning. Hopefully
the planner will use the indexes on the "big" materialized view.
I'm going to try that hack this afternoon too.
I was going to blog about this approach of using a view to do partitioning
of materialized views, but I'm not sure when I'll ever get to it. It was
this list that originally gave me the idea to try this approach. The
partiions are actually materialized views of foreign tables from a Hadoop
cluster.
FWIW, here is the function that builds the view:
---
create or replace function treasure_data."relinkMyView"()
returns varchar
security definer
as
$$
declare
wrMatView varchar;
fromString text;
begin
for wrMatView in
select
c.relname
from
pg_class c
join pg_namespace n on c.relnamespace = n.oid
where
c.relkind = 'm'
and
n.nspname = 'myschema'
and
c.relname ~ 'my_matview_partition_\d\d\d\d_\d\d$'
order by
c.relname
loop
if length(fromString) > 0 then
fromString := format ('%s union all select * from myschema.%I',
fromString, wrMatView);
else
fromString := format ('select * from myschema.%I', wrMatView);
end if;
end loop;
execute format ('create or replace view myschema.my_view as %s',
fromString);
grant select on myschema.my_view to some_read_only_role;
grant select on myschema.my_view to some_read_write_role;
return format ('create or replace view myschema.my_view as %s',
fromString);
end
$$ language plpgsql
;
---
To swap a partition out, I rename it to something that does not conform to
the regex pattern above, and then run the function.
To swap a partition in, I rename it to something that does conform to the
regex pattern, and then run the function.
(of course, that is mostly automated, but it works by hand too)
This has been working great for us until we jumped to PG 10, when suddenly
I can't get the planner to use the indexes in the partitions any more.
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: postgresql 10.1 wrong plan in when using partitions bug
@ 2018-02-05 19:19 Justin Pryzby <pryzby@telsasoft.com>
parent: Mariel Cherkassky <mariel.cherkassky@gmail.com>
0 siblings, 0 replies; 19+ messages in thread
From: Justin Pryzby @ 2018-02-05 19:19 UTC (permalink / raw)
To: Mariel Cherkassky <mariel.cherkassky@gmail.com>; +Cc: Andreas Kretschmer <andreas@a-kretschmer.de>; pgsql-performance@lists.postgresql.org
On Sun, Feb 04, 2018 at 05:28:52PM +0200, Mariel Cherkassky wrote:
> I read those two links and I dont think that they are relevant because : 1
> 1)I didnt do any join.
> 2)I used a where clause in my select
https://www.postgresql.org/docs/current/static/ddl-partitioning.html
|The following caveats apply to constraint exclusion:
| Constraint exclusion only works when the query's WHERE clause contains
|constants (or externally supplied parameters). For example, a comparison
|against a non-immutable function such as CURRENT_TIMESTAMP cannot be optimized,
|since the planner cannot know which partition the function value might fall
|into at run time.
[..]
The issue is with the comparison between function call to to_date() compared
with constant - that doesn't allow constraint exclusion as currently
implemented.
Justin
2018-02-04 16:54 GMT+02:00 Andreas Kretschmer <andreas@a-kretschmer.de>:
> > Am 04.02.2018 um 13:19 schrieb Mariel Cherkassky:
> > > >
> > > >> I checked the plan of the next query :
> > > >> explain select count(*) from log_full where end_date between
> > > >> to_date('2017/12/03','YY/MM/DD') and to_date('2017/12/03','YY/MM/
> > DD');
> > > >>
> > > >>
> > > > can you rewrite the query to
> > > >
> > > > ... where end_date between '2017/12/03' and '2017/12/03'
> > > >
> > > > maybe the planner should be smart enough to do that for you, but obvously
> > > > he can't. So it's a workaround, but it seems to solve the problem.
^ permalink raw reply [nested|flat] 19+ messages in thread
end of thread, other threads:[~2018-02-05 19:19 UTC | newest]
Thread overview: 19+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2018-02-04 10:14 postgresql 10.1 wrong plan in when using partitions bug Mariel Cherkassky <mariel.cherkassky@gmail.com>
2018-02-04 12:03 ` Tomas Vondra <tomas.vondra@2ndquadrant.com>
2018-02-04 12:19 ` Mariel Cherkassky <mariel.cherkassky@gmail.com>
2018-02-04 13:19 ` legrand legrand <legrand_legrand@hotmail.com>
2018-02-04 13:22 ` Mariel Cherkassky <mariel.cherkassky@gmail.com>
2018-02-04 13:29 ` legrand legrand <legrand_legrand@hotmail.com>
2018-02-04 13:43 ` Mariel Cherkassky <mariel.cherkassky@gmail.com>
2018-02-04 14:41 ` Mariel Cherkassky <mariel.cherkassky@gmail.com>
2018-02-04 13:38 ` Rick Otten <rottenwindfish@gmail.com>
2018-02-04 14:54 ` Andreas Kretschmer <andreas@a-kretschmer.de>
2018-02-04 15:06 ` Mariel Cherkassky <mariel.cherkassky@gmail.com>
2018-02-04 15:25 ` Justin Pryzby <pryzby@telsasoft.com>
2018-02-04 15:28 ` Mariel Cherkassky <mariel.cherkassky@gmail.com>
2018-02-05 19:19 ` Justin Pryzby <pryzby@telsasoft.com>
2018-02-04 15:39 ` Tom Lane <tgl@sss.pgh.pa.us>
2018-02-04 15:42 ` Mariel Cherkassky <mariel.cherkassky@gmail.com>
2018-02-04 12:15 ` Rick Otten <rottenwindfish@gmail.com>
2018-02-04 15:35 ` Tom Lane <tgl@sss.pgh.pa.us>
2018-02-04 16:04 ` Rick Otten <rottenwindfish@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox