Re: postgresql 10.1 wrong plan in when using partitions bug - Mailing list pgsql-performance
| From | Rick Otten |
|---|---|
| Subject | Re: postgresql 10.1 wrong plan in when using partitions bug |
| Date | |
| Msg-id | CAMAYy4JzsBvX+LPunq3Pq0+gyBo31x1HyRD+DiJxjwweAtzSbw@mail.gmail.com Whole thread Raw |
| In response to | Re: postgresql 10.1 wrong plan in when using partitions bug (Tom Lane <tgl@sss.pgh.pa.us>) |
| Responses |
Re: failing to use index on UNION of matviews (Re: postgresql 10.1wrong plan in when using partitions bug)
(Justin Pryzby <pryzby@telsasoft.com>)
|
| List | pgsql-performance |
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.
pgsql-performance by date: