Odd performance on update statement

[jordiromera]

I am facing on odd performance on simple update statement (without where condition).

Indeed, on a table with 3600 rows, the following statement takes 2 minutes :

UPDATE dflexnet.dtfxl2_lot
    SET transferflag=1;

With an Oracle IDE, this takes 80ms.

For details, here is the result of oracle_diag() :

oracle_fdw 2.4.0devel, PostgreSQL 11.12 (Debian 11.12-0+deb10u1), Oracle client 21.1.0.0.0

Oracle table :

CREATE TABLE "FLXPMR"."DTFXL2_LOT" (
        "ID" NUMBER NOT NULL ENABLE, 
    "LOT" NVARCHAR2(40), 
    "ARTICLE" NVARCHAR2(20), 
    "CONTENTTYPE" NVARCHAR2(20), 
    "TRANSACTIONID" NUMBER(*,0), 
    "CREATIONUTCDATE" DATE NOT NULL ENABLE, 
    "TRANSFERFLAG" NUMBER(1,0) NOT NULL ENABLE, 
    "TRANSFERLOCALDATE" DATE, 
    "REFID" NUMBER(*,0), 
    "NUM_OF" VARCHAR2(40), 
    "PROPERTIES_NVARCHAR" NVARCHAR2(2000), 
    "PROPERTIES" VARCHAR2(4000), 
     CONSTRAINT "DTFXL2_LOT_UN01" UNIQUE ("ID")
)

PostgreSQL table :

CREATE FOREIGN TABLE dflexnet.dtfxl2_lot(
    id numeric OPTIONS (key 'true') NOT NULL,
    lot character varying(40) NULL COLLATE pg_catalog."default",
    article character varying(20) NULL COLLATE pg_catalog."default",
    contenttype character varying(20) NULL COLLATE pg_catalog."default",
    transactionid numeric NULL,
    creationutcdate timestamp(0) without time zone NOT NULL,
    transferflag smallint NOT NULL,
    transferlocaldate timestamp(0) without time zone NULL,
    refid numeric NULL,
    num_of character varying(40) NULL COLLATE pg_catalog."default",
    properties_nvarchar character varying(2000) NULL COLLATE pg_catalog."default",
    properties json NULL
)
    SERVER dflexnet
    OPTIONS (schema 'FLXPMR', table 'DTFXL2_LOT', sample_percent '1', prefetch '1000');

And here is the EXPLAIN ANALYSE on this query :

[
  {
    "Plan": {
      "Node Type": "ModifyTable",
      "Operation": "Update",
      "Parallel Aware": false,
      "Relation Name": "dtfxl2_lot",
      "Schema": "dflexnet",
      "Alias": "dtfxl2_lot",
      "Startup Cost": 10000,
      "Total Cost": 10010,
      "Plan Rows": 1,
      "Plan Width": 1006,
      "Actual Startup Time": 113797.942,
      "Actual Total Time": 113797.943,
      "Actual Rows": 0,
      "Actual Loops": 1,
      "Oracle statement": "UPDATE \"FLXPMR\".\"DTFXL2_LOT\" SET \"TRANSFERFLAG\" = :p7 WHERE \"ID\" = :k1",
      "Shared Hit Blocks": 0,
      "Shared Read Blocks": 0,
      "Shared Dirtied Blocks": 0,
      "Shared Written Blocks": 0,
      "Local Hit Blocks": 0,
      "Local Read Blocks": 0,
      "Local Dirtied Blocks": 0,
      "Local Written Blocks": 0,
      "Temp Read Blocks": 0,
      "Temp Written Blocks": 0,
      "Plans": [
        {
          "Node Type": "Foreign Scan",
          "Operation": "Select",
          "Parent Relationship": "Member",
          "Parallel Aware": false,
          "Relation Name": "dtfxl2_lot",
          "Schema": "dflexnet",
          "Alias": "dtfxl2_lot",
          "Startup Cost": 10000,
          "Total Cost": 10010,
          "Plan Rows": 1,
          "Plan Width": 1006,
          "Actual Startup Time": 488.304,
          "Actual Total Time": 1314.098,
          "Actual Rows": 3629,
          "Actual Loops": 1,
          "Output": [
            "id",
            "lot",
            "article",
            "contenttype",
            "transactionid",
            "creationutcdate",
            "'1'::smallint",
            "transferlocaldate",
            "refid",
            "num_of",
            "properties_nvarchar",
            "properties",
            "id"
          ],
          "Oracle query": "SELECT /*5432422d82c29ceaaabdc707ae6826d3*/ r1.\"ID\", r1.\"LOT\", r1.\"ARTICLE\", r1.\"CONTENTTYPE\", r1.\"TRANSACTIONID\", r1.\"CREATIONUTCDATE\", r1.\"TRANSFERLOCALDATE\", r1.\"REFID\", r1.\"NUM_OF\", r1.\"PROPERTIES_NVARCHAR\", r1.\"PROPERTIES\" FROM \"FLXPMR\".\"DTFXL2_LOT\" r1 FOR UPDATE",
          "Oracle plan": "      TABLE ACCESS FULL DTFXL2_LOT",
          "Shared Hit Blocks": 0,
          "Shared Read Blocks": 0,
          "Shared Dirtied Blocks": 0,
          "Shared Written Blocks": 0,
          "Local Hit Blocks": 0,
          "Local Read Blocks": 0,
          "Local Dirtied Blocks": 0,
          "Local Written Blocks": 0,
          "Temp Read Blocks": 0,
          "Temp Written Blocks": 0
        }
      ]
    },
    "Planning Time": 671.173,
    "Triggers": [],
    "Execution Time": 113798.105
  }
]

This analysis point out a foreign scan, which seems odd to me, specially without where clause. Maybe there are round trip between the two databases on UPDATE statement ?

Also, I will test soon, this query encapsulates in Oracle procedure.

Thank you

[jordiromera]

Now with an Orale procedure, the following statement takes 300ms :

select oracle_execute('dflexnet', 'BEGIN CONS_IS_FXL2.SetTransferFlag_LOT(1, sysdate); END;')

And here is the code of procedure :

CREATE OR REPLACE PACKAGE BODY FLXPMR.CONS_IS_FXL2 AS

  PROCEDURE SetTransferFlag_LOT(
    iTransferFlag IN DTFXL2_LOT.TRANSFERFLAG%TYPE,
    iTransferdate IN DTFXL2_LOT.TRANSFERLOCALDATE%TYPE
    ) AS
  BEGIN

    UPDATE DTFXL2_LOT 
    SET 
        TRANSFERFLAG = iTransferFlag,
        TRANSFERLOCALDATE= iTransferdate;

  END SetTransferFlag_LOT;
END CONS_IS_FXL2;

Note : Another field (TRANSFERLOCALDATE) is update in this procedure.

[laurenz]

Yes, that is to be expected, because of the way that UPDATE on foreign tables works in PostgreSQL:

• the rows to be updated are read (this will be all rows)
• for each row that should be updated, an UPDATE statement is executed on the remote database

It is the number of round trips between PostgreSQL and Oracle (one per row updated) that will kill your performance. This is essentially the same problem as #260.

Bulk data modification is not efficient with oracle_fdw.

[jordiromera]

Welcome back !

OK, thank you for explanations. A workaround was found, and this issue should be closed.

Nevertheless, I am curious and, is it a limitation of oracle_fdw or any foreign data wrapper from PostgreSQL ?

[laurenz]

That is a limitation of the FDW API in general.

In principle, you could work around it by actually running the UPDATE instead of the Foreign Scan and doing nothing for the ModifyTable, but that is not trivial, and I am not motivated to do it.