You can try and run the query, *to see how long it takes*. Do that both from psql and mysql. That'll tell you *something* about where the problem is.
On Thu, Feb 26, 2026 at 7:09 PM Michael Nolan <[email protected]> wrote: > You mean this one: > > CREATE MATERIALIZED VIEW public.memmast AS > SELECT memmast_simulant.civicrm_contact_id, > memmast_simulant.memid, > .... > FROM public.memmast_simulant > WITH NO DATA; > > And here's the foreign table: > > CREATE FOREIGN TABLE public.memmast_simulant ( > civicrm_contact_id integer, > ... > SERVER mysql_civicrm > OPTIONS ( > dbname 'skvare8_uscf_civicrm', > table_name 'memmast_simulant' > ); > > > My understanding is that this view on mysql pulls fields from a lot of > different mysql tables because that's how CIVI-CRM organizes data. > > I can try running that with explain, but my understanding is that foreign > tables can't be analyzed. > > Mike Nolan > > > On Thu, Feb 26, 2026 at 6:01 PM Ron Johnson <[email protected]> > wrote: > >> There has to be a view definition stored *somewhere* in the PG database >> catalog. Find it and run the SELECT statement. >> >> That'll at least narrow down the problem (as well as letting you run >> EXPLAIN on it). >> >> On Thu, Feb 26, 2026 at 6:48 PM Michael Nolan <[email protected]> wrote: >> >>> I can run select statements on the mysql server from the azure server, >>> but the command we're using to refresh the matview is: >>> REFRESH MATERIALIZED VIEW memmast_ratings; >>> >>> Mike Nolan >>> >>> On Thu, Feb 26, 2026 at 5:45 PM Ron Johnson <[email protected]> >>> wrote: >>> >>>> Mike, >>>> >>>> 1. You're using mysql_fdw to connect? >>>> >>>> 2. What happens when you just run the raw SELECT statement (redirecting >>>> stdout to /dev/null, since we only need timings and error messages) from >>>> psql? >>>> >>>> >>>> On Thu, Feb 26, 2026 at 6:37 PM Michael Nolan <[email protected]> wrote: >>>> >>>>> My understanding is that the mysql server is at a Linode facility in >>>>> PA, the current production postgres server is in Asheville NC, and the >>>>> intended new server is a Azure server in the eastern US, not sure exactly >>>>> where. >>>>> >>>>> The two small matviews refresh, the two bigger ones fail, so it seems >>>>> size-related, which is why I was wondering if the settings might make a >>>>> difference because this server isn't fully production-scale yet. But >>>>> weve transferred 175 GB files to it in about 6 hours so I think the net >>>>> connection itself is probably not the issue. >>>>> >>>>> Mike Nolan >>>>> >>>>> On Thu, Feb 26, 2026 at 4:11 PM Adrian Klaver < >>>>> [email protected]> wrote: >>>>> >>>>>> On 2/26/26 1:59 PM, Michael Nolan wrote: >>>>>> > We have a connection from a PostgreSQL server to a MySQL server >>>>>> which is >>>>>> > used to update a materialized view on the PostgreSQL server from >>>>>> tables >>>>>> > on the MySQL server (running CIVI-CRM, which may not be relevant.) >>>>>> > >>>>>> > We are trying to move the PostgreSQL server to a new cloud server. >>>>>> > >>>>>> > On the current production system, all the materialized views work, >>>>>> but >>>>>> > the biggest of them can take about an hour. >>>>>> > >>>>>> > On the new system, the smallest of the materialized views works, >>>>>> but the >>>>>> > larger ones all seem to time out. >>>>>> >>>>>> "... move the PostgreSQL server to a new cloud server", where is that >>>>>> relative to the MySQL server compared to old Postgres server? >>>>>> >>>>>> > >>>>>> > Could this be some kind of setting on PostgreSQL, like a memory or >>>>>> > buffer issue? >>>>>> > >>>>>> > Mike Nolan >>>>>> > [email protected] <mailto:[email protected]> >>>>>> >>>>>> >>>>>> -- >>>>>> Adrian Klaver >>>>>> [email protected] >>>>>> >>>>> >>>> >>>> -- >>>> Death to <Redacted>, and butter sauce. >>>> Don't boil me, I'm still alive. >>>> <Redacted> lobster! >>>> >>> >> >> -- >> Death to <Redacted>, and butter sauce. >> Don't boil me, I'm still alive. >> <Redacted> lobster! >> > -- Death to <Redacted>, and butter sauce. Don't boil me, I'm still alive. <Redacted> lobster!
