Thread: postgresql 10.1 wrong plan in when using partitions bug
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/
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.
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
On Sun, Feb 4, 2018 at 5:14 AM, Mariel Cherkassky <mariel.cherkassky@gmail.com> wrote:
None of the various queries using these views on top of my hand constructed "partitions" are using indexes.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_fulllog_full_01_11_2017 -->log_full _01_11_2017_x1log_full _01_11_2017_x2log_full _01_11_2017_x3log_full _01_11_2017_x4log_full_02_11_2017log_full _02_11_2017_x1log_full _02_11_2017_x2log_full _02_11_2017_x3log_full _02_11_2017_x4and 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:40I 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.
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.)
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 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>:
You haven't shown us how the partitions are defined, nor the query plan.
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 ?
>
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
What is the value of guc constrain_exclusion ? -- Sent from: http://www.postgresql-archive.org/PostgreSQL-performance-f2050081.html
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
Explain analyse Output ? -- Sent from: http://www.postgresql-archive.org/PostgreSQL-performance-f2050081.html
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.
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..
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..
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
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
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.
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.
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
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
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
On Sun, Feb 4, 2018 at 10:35 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
My hope is that 10.2 will fix our issue "by accident" rather than on purpose.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).
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.
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.
Re: failing to use index on UNION of matviews (Re: postgresql 10.1wrong plan in when using partitions bug)
From
Justin Pryzby
Date:
On Sun, Feb 04, 2018 at 11:04:56AM -0500, Rick Otten wrote: > 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. I think it'd be useful to see the plan from explain analyze, on both the "parent" view and a child, with and without SET enable_seqscan=off, Justin
Re: failing to use index on UNION of matviews (Re: postgresql 10.1wrong plan in when using partitions bug)
From
Rick Otten
Date:
Ooo. I wasn't aware of that option. (Learn something new every day!)
Setting enable_seqscan=off takes one of the shorter queries I was working with from about 3 minutes to 300ms. This is a comparable performance improvement to where I put a materialized view (with indexes) on top of the materialized views instead of using a simple view on top of the materialized views. I'll have to try it with the query that takes 12 hours.
I built a test case, but can't get it to reproduce what I'm seeing on my production database (it keeps choosing the indexes). I'm still fiddling with that test case so I can easily share it. I'm also back to trying to figure out what is different between my laptop database and the test case I built and the real world query with the real data, and pondering the worst query itself to see if some sort of re-write will help.
On Tue, Feb 6, 2018 at 1:18 PM, Justin Pryzby <pryzby@telsasoft.com> wrote:
On Sun, Feb 04, 2018 at 11:04:56AM -0500, Rick Otten wrote:
> 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.
I think it'd be useful to see the plan from explain analyze, on both the
"parent" view and a child, with and without SET enable_seqscan=off,
Justin
Re: failing to use index on UNION of matviews (Re: postgresql 10.1wrong plan in when using partitions bug)
From
Rick Otten
Date:
On Tue, Feb 6, 2018 at 3:02 PM, Rick Otten <rottenwindfish@gmail.com> wrote:
Ooo. I wasn't aware of that option. (Learn something new every day!)Setting enable_seqscan=off takes one of the shorter queries I was working with from about 3 minutes to 300ms. This is a comparable performance improvement to where I put a materialized view (with indexes) on top of the materialized views instead of using a simple view on top of the materialized views. I'll have to try it with the query that takes 12 hours.I built a test case, but can't get it to reproduce what I'm seeing on my production database (it keeps choosing the indexes). I'm still fiddling with that test case so I can easily share it. I'm also back to trying to figure out what is different between my laptop database and the test case I built and the real world query with the real data, and pondering the worst query itself to see if some sort of re-write will help.On Tue, Feb 6, 2018 at 1:18 PM, Justin Pryzby <pryzby@telsasoft.com> wrote:On Sun, Feb 04, 2018 at 11:04:56AM -0500, Rick Otten wrote:
> 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.
I think it'd be useful to see the plan from explain analyze, on both the
"parent" view and a child, with and without SET enable_seqscan=off,
Justin
Sorry, I didn't mean to "top reply". My bad.
Re: failing to use index on UNION of matviews (Re: postgresql 10.1wrong plan in when using partitions bug)
From
Rick Otten
Date:
Setting enable_seqscan=off takes one of the shorter queries I was working with from about 3 minutes to 300ms. This is a comparable performance improvement to where I put a materialized view (with indexes) on top of the materialized views instead of using a simple view on top of the materialized views. I'll have to try it with the query that takes 12 hours.
The query that takes 12 hours and won't use indexes when I feel it should is a materialized view refresh. When I set it before testing the plan with a simple explain on the query it definitely gets it to use all of the indexes. Does setting something like "enable_seqscan=off" work when I follow it with a "refresh materialized view concurrently" instead of a simple select? I'll try it to see if it helps the refresh time, but I thought I'd ask.
(I got pulled into another problem since my last email, so I haven't had a chance to follow up.)