Re: failing to use index on UNION of matviews (Re: postgresql 10.1 wrong plan in when using partitions bug)
> > >> >> 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.)
Re: failing to use index on UNION of matviews (Re: postgresql 10.1 wrong plan in when using partitions bug)
On Tue, Feb 6, 2018 at 3:02 PM, Rick Otten 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 > 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 wrote: >> > >> > > Rick Otten 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.1 wrong plan in when using partitions bug)
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 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 wrote: > > > > > Rick Otten 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.1 wrong plan in when using partitions bug)
On Sun, Feb 04, 2018 at 11:04:56AM -0500, Rick Otten wrote: > On Sun, Feb 4, 2018 at 10:35 AM, Tom Lane wrote: > > > Rick Otten 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: postgresql 10.1 wrong plan in when using partitions bug
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 :
> > 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: postgresql 10.1 wrong plan in when using partitions bug
On Sun, Feb 4, 2018 at 10:35 AM, Tom Lane wrote:
> Rick Otten 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.
Re: postgresql 10.1 wrong plan in when using partitions bug
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 : > Mariel Cherkassky 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 >
Re: postgresql 10.1 wrong plan in when using partitions bug
Mariel Cherkassky 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
Re: postgresql 10.1 wrong plan in when using partitions bug
Rick Otten 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
Re: postgresql 10.1 wrong plan in when using partitions bug
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 :
> 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 :
> >
> > >
> > >
> 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: postgresql 10.1 wrong plan in when using partitions bug
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 :
>
> >
> >
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: postgresql 10.1 wrong plan in when using partitions bug
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 :
>
>
> 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
>
>
>
Re: postgresql 10.1 wrong plan in when using partitions bug
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
Re: postgresql 10.1 wrong plan in when using partitions bug
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 :
> 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 :
>
>> Explain analyse
>> Output ?
>>
>>
>>
>> --
>> Sent from: http://www.postgresql-archive.org/PostgreSQL-performance-f20
>> 50081.html
>>
>>
>
Re: postgresql 10.1 wrong plan in when using partitions bug
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 :
> Explain analyse
> Output ?
>
>
>
> --
> Sent from: http://www.postgresql-archive.org/PostgreSQL-performance-
> f2050081.html
>
>
Re: postgresql 10.1 wrong plan in when using partitions bug
On Sun, Feb 4, 2018 at 8:19 AM, legrand legrand 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.
Re: postgresql 10.1 wrong plan in when using partitions bug
Explain analyse Output ? -- Sent from: http://www.postgresql-archive.org/PostgreSQL-performance-f2050081.html
Re: postgresql 10.1 wrong plan in when using partitions bug
show constraint_exclusion; constraint_exclusion -- partition (1 row) 2018-02-04 15:19 GMT+02:00 legrand legrand : > What is the value of guc constrain_exclusion ? > > > > -- > Sent from: http://www.postgresql-archive.org/PostgreSQL-performance- > f2050081.html > >
Re: postgresql 10.1 wrong plan in when using partitions bug
What is the value of guc constrain_exclusion ? -- Sent from: http://www.postgresql-archive.org/PostgreSQL-performance-f2050081.html
Re: postgresql 10.1 wrong plan in when using partitions bug
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 :
>
>
> 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 : -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
>
Re: postgresql 10.1 wrong plan in when using partitions bug
On Sun, Feb 4, 2018 at 5:14 AM, Mariel Cherkassky <
mariel.cherkas...@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 : -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.)
Re: postgresql 10.1 wrong plan in when using partitions bug
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 : -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
