Thread

1. materialized view refresh of a foreign table

   Rick Otten <rottenwindfish@gmail.com> — 2019-06-23T14:21:40Z

   I'm not sure where else to look, so I'm asking here for tips.
   
   I have a table in a remote (Heroku-managed) postgresql database (PG 10.7).
   
   On the other end, (PG 11.3) I have a foreign table configured with a
   materialized view in front of it.
   
   Up until Thursday evening, it was taking about 12 - 15 seconds to refresh,
   it is only 15,000 rows with 41 columns.   Since Thursday evening it has
   been taking 15 _minutes_ or more to refresh.  Nothing changed on my end
   that I'm aware of.  It completes, it just takes forever.
   
   Here is a summary of what I've tried:
   
   1) Refreshing the materialized views of other tables from that same source
   database, some much bigger, still perform within seconds as they always
   have.
   2) Dropping the foreign table and the materialized view and recreating them
   didn't help.
   3) It doesn't matter whether I refresh concurrently or not.
   4) Configuring the foreign table and materialized view on my laptop's
   postgresql instance exhibited the same behavior for just this one table.
   5) Querying the foreign table directly for a specific row was fast.
   6) Reindex and vacuum full analyze on the source table didn't help.
   7) Bumping the database on my end to 11.4, didn't help.
   8) There are no locks on either database that I can see while the query
   appears to be stalled.
   9) Running the materialized view select directly against the source table
   completes within seconds.
   10) Running the materialized view select directly against the foreign table
   also completes within a few seconds.
   11) Dropping all of the indexes on the materialized view, including the
   unique one and running the refresh (without 'concurrently') does not help.
   
   I feel like I'm missing something obvious here, but I'm just not seeing
   it.  Any thoughts about where else to look?
   

2. Re: materialized view refresh of a foreign table

   Rick Otten <rottenwindfish@gmail.com> — 2019-06-25T11:03:31Z

   On Sun, Jun 23, 2019 at 10:21 AM Rick Otten <rottenwindfish@gmail.com>
   wrote:
   
   > I'm not sure where else to look, so I'm asking here for tips.
   >
   > I have a table in a remote (Heroku-managed) postgresql database (PG 10.7).
   >
   > On the other end, (PG 11.3) I have a foreign table configured with a
   > materialized view in front of it.
   >
   > Up until Thursday evening, it was taking about 12 - 15 seconds to refresh,
   > it is only 15,000 rows with 41 columns.   Since Thursday evening it has
   > been taking 15 _minutes_ or more to refresh.  Nothing changed on my end
   > that I'm aware of.  It completes, it just takes forever.
   >
   >
   I believe I've solved this mystery.  Thanks for hearing me out.  Just the
   opportunity to summarize everything I'd tried helped me discover the root
   cause.
   
   In the middle of the table there is a 'text' column.   Since last Thursday
   there were a number of rows that were populated with very long strings.
   (lots of text in that column).   This appears to have completely bogged
   down the materialized view refresh.  Since we weren't using that column in
   our analytics database at this time, I simply removed it from the
   materialized view.  If we do end up needing it, I'll give it its own
   materialized view and/or look at chopping up the text into just the bits we
   need.