Re: Performance degradation of REFRESH MATERIALIZED VIEW

On Tue, Apr 27, 2021 at 7:13 PM Tomas Vondra
<tomas(dot)vondra(at)enterprisedb(dot)com> wrote:
> What Andres is suggesting (I think) is to modify ExecInsert() to pass a
> valid bistate to table_tuple_insert, instead of just NULL, and store the
> vmbuffer in it. Not sure how to identify when inserting more than just a
> single row, though ...

I think the thread "should INSERT SELECT use a BulkInsertState?" [1],
has a simple dynamic mechanism [with a GUC defining the threshold
tuples] to switch over to using BulkInsertState. Maybe it's worth
having a look at the patch -
0001-INSERT-SELECT-to-use-BulkInsertState-and-multi_i.patch?

+ /* Use bulk insert after a threshold number of tuples */
+ // XXX: maybe this should only be done if it's not a partitioned table or
+ // if the partitions don't support miinfo, which uses its own bistates
+ mtstate->ntuples++;
+ if (mtstate->bistate == NULL &&
+ mtstate->operation == CMD_INSERT &&
+ mtstate->ntuples > bulk_insert_ntuples &&
+ bulk_insert_ntuples >= 0)
+ {
+ elog(DEBUG1, "enabling bulk insert");
+ mtstate->bistate = GetBulkInsertState();
+ }

[1] https://www.postgresql.org/message-id/20210222030158.GS14772%40telsasoft.com

With Regards,
Bharath Rupireddy.
EnterpriseDB: http://www.enterprisedb.com