On Tue, Feb 6, 2018 at 3:02 PM, Rick Otten <rottenwindf...@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 <pry...@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 <t...@sss.pgh.pa.us> wrote: >> > >> > > Rick Otten <rottenwindf...@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.
