search

EnterpriseDB / mysql_fdw

PostgreSQL foreign data wrapper for MySQL
Other
521 stars 160 forks source link

Running in JavaWeb Project , WHERE clause become partial push-down after 9 queries. #220

Open MondayLiu opened 3 years ago

MondayLiu commented 3 years ago

Hi, All: I'm running in JavaWeb Project , WHERE clause become partial push-down after 9 queries.

Steps for reproduce:

1. PG11.5&MySQL_FDW2.5.5&MySQL5.7.21

2. DDL (database diagram)

      -- 2.1. PG
CREATE TABLE DDTINTO_MASTER2  (
  AC_DATE   int                 NOT NULL DEFAULT 0,
  AC_NO         varchar(12)         NOT NULL DEFAULT '',
  CCY       varchar(3)          NOT NULL DEFAULT '',
  QUALIFIER     char(1)             NOT NULL DEFAULT '',
  DEP       decimal(16, 2)         NOT NULL DEFAULT 0.00,
  primary key (AC_DATE, AC_NO, CCY, QUALIFIER)
) PARTITION BY RANGE (AC_DATE);

CREATE TABLE ddtinto2_202103 PARTITION OF DDTINTO_MASTER2 FOR VALUES FROM (0) TO (20210401);

CREATE FOREIGN TABLE DDTINTO_CURRENT2(
  AC_DATE   int                 NOT NULL DEFAULT 0,
  AC_NO         varchar(12)         NOT NULL DEFAULT '',
  CCY       varchar(3)          NOT NULL DEFAULT '',
  QUALIFIER     char(1)             NOT NULL DEFAULT '',
  DEP       decimal(16, 2)          NOT NULL DEFAULT 0.00
)
SERVER mysql_server
OPTIONS (dbname 'ocbs387', table_name 'view_ddtinto_current2');

CREATE VIEW DDTINTO2 AS
SELECT * FROM DDTINTO_CURRENT2
UNION ALL
SELECT * FROM DDTINTO_MASTER2;

 -- 2.2. MySQL
CREATE TABLE DDTINTO2  (
  AC_DATE   int                 NOT NULL DEFAULT 0,
  AC_NO         char(12)            NOT NULL DEFAULT '',
  CCY       char(3)             NOT NULL DEFAULT '',
  QUALIFIER     char(1)                 NOT NULL DEFAULT '',
  DEP       decimal(16, 2)      NOT NULL DEFAULT 0.00,
  primary key (AC_DATE, AC_NO, CCY, QUALIFIER)
);

create view view_ddtinto_current2
as
select * from ddtinto2
where    ac_date >= 20210401;

3.DML

  -- 3.1 PG
INSERT INTO DDTINTO_MASTER2 VALUES
 (20210301,'403065121762','USD','A', 1.24)
,(20210302,'403065121762','USD','A', 1.24)
,(20210303,'403065121762','USD','A', 1.24)
,(20210304,'403065121762','USD','A', 1.24)
,(20210305,'403065121762','USD','A', 1.24);

    -- 3.2 MySQL
INSERT INTO DDTINTO2 VALUES
 (20210401,'403065121762','USD','A', 1.24)
,(20210402,'403065121762','USD', 'A',1.24)
,(20210403,'403065121762','USD','A', 1.24)
,(20210404,'403065121762','USD','A', 1.24)
,(20210405,'403065121762','USD','A', 1.24);

4. Java web project github 4.1 project environment

    jdk8
    springboot1.5.13.RELEASE
    maven3.6.1
    hikariCP2.5.1
    MyBatis3.4.6

5. Reproduce 5.1 Cannot be reproduce when execute the query(with exact values) on psql. 5.2 Request this GET request ten times : GET REQUEST 5.3 MySQL Slow query log(Pay special attention to the 10th query)

2021-06-09T12:55:37.532947Z    69 Connect   canal@PC-SZ-033.ZATI.COM on ocbs387 using TCP/IP
2021-06-09T12:55:37.533221Z    69 Query SET sql_mode='ANSI_QUOTES'
2021-06-09T12:55:37.533667Z    69 Query SET sql_mode='ANSI_QUOTES'
2021-06-09T12:55:37.534705Z    69 Prepare   SELECT `ac_date`, `ac_no`, `ccy`, `qualifier`, `dep` FROM `ocbs387`.`view_ddtinto_current2` WHERE (`qualifier`  IN ('A', 'C')) AND ((`ac_date` >= 20210401)) AND ((`ac_date` <= 20210531)) AND ((`ac_no` = '403065121762')) AND ((`ccy` = 'USD'))
2021-06-09T12:55:37.535840Z    69 Close stmt
2021-06-09T12:55:38.218395Z    69 Query SET sql_mode='ANSI_QUOTES'
2021-06-09T12:55:38.218571Z    69 Query SET sql_mode='ANSI_QUOTES'
2021-06-09T12:55:38.218816Z    69 Prepare   SELECT `ac_date`, `ac_no`, `ccy`, `qualifier`, `dep` FROM `ocbs387`.`view_ddtinto_current2` WHERE (`qualifier`  IN ('A', 'C')) AND ((`ac_date` >= 20210401)) AND ((`ac_date` <= 20210531)) AND ((`ac_no` = '403065121762')) AND ((`ccy` = 'USD'))
2021-06-09T12:55:38.219643Z    69 Close stmt
2021-06-09T12:55:38.737659Z    69 Query SET sql_mode='ANSI_QUOTES'
2021-06-09T12:55:38.737899Z    69 Query SET sql_mode='ANSI_QUOTES'
2021-06-09T12:55:38.738154Z    69 Prepare   SELECT `ac_date`, `ac_no`, `ccy`, `qualifier`, `dep` FROM `ocbs387`.`view_ddtinto_current2` WHERE (`qualifier`  IN ('A', 'C')) AND ((`ac_date` >= 20210401)) AND ((`ac_date` <= 20210531)) AND ((`ac_no` = '403065121762')) AND ((`ccy` = 'USD'))
2021-06-09T12:55:38.739003Z    69 Close stmt
2021-06-09T12:55:39.238398Z    69 Query SET sql_mode='ANSI_QUOTES'
2021-06-09T12:55:39.238658Z    69 Query SET sql_mode='ANSI_QUOTES'
2021-06-09T12:55:39.238930Z    69 Prepare   SELECT `ac_date`, `ac_no`, `ccy`, `qualifier`, `dep` FROM `ocbs387`.`view_ddtinto_current2` WHERE (`qualifier`  IN ('A', 'C')) AND ((`ac_date` >= 20210401)) AND ((`ac_date` <= 20210531)) AND ((`ac_no` = '403065121762')) AND ((`ccy` = 'USD'))
2021-06-09T12:55:39.239785Z    69 Close stmt
2021-06-09T12:55:39.840982Z    69 Query SET sql_mode='ANSI_QUOTES'
2021-06-09T12:55:39.841242Z    69 Query SET sql_mode='ANSI_QUOTES'
2021-06-09T12:55:39.841520Z    69 Prepare   SELECT `ac_date`, `ac_no`, `ccy`, `qualifier`, `dep` FROM `ocbs387`.`view_ddtinto_current2` WHERE (`qualifier`  IN ('A', 'C')) AND ((`ac_date` >= 20210401)) AND ((`ac_date` <= 20210531)) AND ((`ac_no` = '403065121762')) AND ((`ccy` = 'USD'))
2021-06-09T12:55:39.842373Z    69 Close stmt
2021-06-09T12:55:40.364438Z    69 Query SET sql_mode='ANSI_QUOTES'
2021-06-09T12:55:40.364727Z    69 Query SET sql_mode='ANSI_QUOTES'
2021-06-09T12:55:40.365023Z    69 Prepare   SELECT `ac_date`, `ac_no`, `ccy`, `qualifier`, `dep` FROM `ocbs387`.`view_ddtinto_current2` WHERE (`qualifier`  IN ('A', 'C')) AND ((`ac_date` >= 20210401)) AND ((`ac_date` <= 20210531)) AND ((`ac_no` = '403065121762')) AND ((`ccy` = 'USD'))
2021-06-09T12:55:40.365870Z    69 Close stmt
2021-06-09T12:55:40.865010Z    69 Query SET sql_mode='ANSI_QUOTES'
2021-06-09T12:55:40.865277Z    69 Query SET sql_mode='ANSI_QUOTES'
2021-06-09T12:55:40.865568Z    69 Prepare   SELECT `ac_date`, `ac_no`, `ccy`, `qualifier`, `dep` FROM `ocbs387`.`view_ddtinto_current2` WHERE (`qualifier`  IN ('A', 'C')) AND ((`ac_date` >= 20210401)) AND ((`ac_date` <= 20210531)) AND ((`ac_no` = '403065121762')) AND ((`ccy` = 'USD'))
2021-06-09T12:55:40.866604Z    69 Close stmt
2021-06-09T12:55:41.376092Z    69 Query SET sql_mode='ANSI_QUOTES'
2021-06-09T12:55:41.376306Z    69 Query SET sql_mode='ANSI_QUOTES'
2021-06-09T12:55:41.376551Z    69 Prepare   SELECT `ac_date`, `ac_no`, `ccy`, `qualifier`, `dep` FROM `ocbs387`.`view_ddtinto_current2` WHERE (`qualifier`  IN ('A', 'C')) AND ((`ac_date` >= 20210401)) AND ((`ac_date` <= 20210531)) AND ((`ac_no` = '403065121762')) AND ((`ccy` = 'USD'))
2021-06-09T12:55:41.377362Z    69 Close stmt
2021-06-09T12:55:41.912097Z    69 Query SET sql_mode='ANSI_QUOTES'
2021-06-09T12:55:41.912323Z    69 Query SET sql_mode='ANSI_QUOTES'
2021-06-09T12:55:41.912609Z    69 Prepare   SELECT `ac_date`, `ac_no`, `ccy`, `qualifier`, `dep` FROM `ocbs387`.`view_ddtinto_current2` WHERE (`qualifier`  IN ('A', 'C')) AND ((`ac_date` >= 20210401)) AND ((`ac_date` <= 20210531)) AND ((`ac_no` = '403065121762')) AND ((`ccy` = 'USD'))
2021-06-09T12:55:41.913490Z    69 Close stmt
2021-06-09T12:55:42.441026Z    69 Query SET sql_mode='ANSI_QUOTES'
2021-06-09T12:55:42.441439Z    69 Query SET sql_mode='ANSI_QUOTES'
2021-06-09T12:55:42.441705Z    69 Prepare   SELECT `ac_date`, `ac_no`, `ccy`, `qualifier`, `dep` FROM `ocbs387`.`view_ddtinto_current2` WHERE (`qualifier`  IN ('A', 'C')) AND ((`ac_date` >= ?)) AND ((`ac_date` <= ?))
2021-06-09T12:55:42.442745Z    69 Close stmt
MondayLiu commented 3 years ago

Hi, All: The execution result on psql（The difference is one more line：SET sql_mode='ANSI_QUOTES'）:

2021-06-10T02:51:53.779657Z    70 Query SET sql_mode='ANSI_QUOTES'
2021-06-10T02:51:53.779950Z    70 Query SET sql_mode='ANSI_QUOTES'
2021-06-10T02:51:53.780240Z    70 Prepare   SELECT `ac_date`, `ac_no`, `ccy`, `qualifier`, `dep` FROM `ocbs387`.`view_ddtinto_current2` WHERE (`qualifier`  IN ('A', 'C')) AND ((`ac_date` >= 20210401)) AND ((`ac_date` <= 20210531)) AND ((`ac_no` = '403065121762')) AND ((`ccy` = 'USD'))
2021-06-10T02:51:53.780892Z    70 Close stmt    
2021-06-10T02:51:53.781093Z    70 Query SET sql_mode='ANSI_QUOTES'
2021-06-10T02:51:53.781404Z    70 Prepare   SELECT `ac_date`, `ac_no`, `ccy`, `qualifier`, `dep` FROM `ocbs387`.`view_ddtinto_current2` WHERE (`qualifier`  IN ('A', 'C')) AND ((`ac_date` >= 20210401)) AND ((`ac_date` <= 20210531)) AND ((`ac_no` = '403065121762')) AND ((`ccy` = 'USD'))
2021-06-10T02:51:53.781766Z    70 Close stmt    
2021-06-10T02:51:53.781848Z    70 Query SET sql_mode='ANSI_QUOTES'
2021-06-10T02:51:53.781996Z    70 Prepare   SELECT `ac_date`, `ac_no`, `ccy`, `qualifier`, `dep` FROM `ocbs387`.`view_ddtinto_current2` WHERE (`qualifier`  IN ('A', 'C')) AND ((`ac_date` >= 20210401)) AND ((`ac_date` <= 20210531)) AND ((`ac_no` = '403065121762')) AND ((`ccy` = 'USD'))
2021-06-10T02:51:53.782232Z    70 Close stmt    
2021-06-10T02:51:53.782303Z    70 Query SET sql_mode='ANSI_QUOTES'
2021-06-10T02:51:53.782434Z    70 Prepare   SELECT `ac_date`, `ac_no`, `ccy`, `qualifier`, `dep` FROM `ocbs387`.`view_ddtinto_current2` WHERE (`qualifier`  IN ('A', 'C')) AND ((`ac_date` >= 20210401)) AND ((`ac_date` <= 20210531)) AND ((`ac_no` = '403065121762')) AND ((`ccy` = 'USD'))
2021-06-10T02:51:53.782655Z    70 Close stmt    
2021-06-10T02:51:53.782722Z    70 Query SET sql_mode='ANSI_QUOTES'
2021-06-10T02:51:53.782850Z    70 Prepare   SELECT `ac_date`, `ac_no`, `ccy`, `qualifier`, `dep` FROM `ocbs387`.`view_ddtinto_current2` WHERE (`qualifier`  IN ('A', 'C')) AND ((`ac_date` >= 20210401)) AND ((`ac_date` <= 20210531)) AND ((`ac_no` = '403065121762')) AND ((`ccy` = 'USD'))
2021-06-10T02:51:53.783067Z    70 Close stmt    
2021-06-10T02:51:53.783133Z    70 Query SET sql_mode='ANSI_QUOTES'
2021-06-10T02:51:53.783259Z    70 Prepare   SELECT `ac_date`, `ac_no`, `ccy`, `qualifier`, `dep` FROM `ocbs387`.`view_ddtinto_current2` WHERE (`qualifier`  IN ('A', 'C')) AND ((`ac_date` >= 20210401)) AND ((`ac_date` <= 20210531)) AND ((`ac_no` = '403065121762')) AND ((`ccy` = 'USD'))
2021-06-10T02:51:53.783476Z    70 Close stmt    
2021-06-10T02:51:53.783544Z    70 Query SET sql_mode='ANSI_QUOTES'
2021-06-10T02:51:53.783725Z    70 Prepare   SELECT `ac_date`, `ac_no`, `ccy`, `qualifier`, `dep` FROM `ocbs387`.`view_ddtinto_current2` WHERE (`qualifier`  IN ('A', 'C')) AND ((`ac_date` >= 20210401)) AND ((`ac_date` <= 20210531)) AND ((`ac_no` = '403065121762')) AND ((`ccy` = 'USD'))
2021-06-10T02:51:53.784068Z    70 Close stmt    
2021-06-10T02:51:53.784144Z    70 Query SET sql_mode='ANSI_QUOTES'
2021-06-10T02:51:53.784419Z    70 Prepare   SELECT `ac_date`, `ac_no`, `ccy`, `qualifier`, `dep` FROM `ocbs387`.`view_ddtinto_current2` WHERE (`qualifier`  IN ('A', 'C')) AND ((`ac_date` >= 20210401)) AND ((`ac_date` <= 20210531)) AND ((`ac_no` = '403065121762')) AND ((`ccy` = 'USD'))
2021-06-10T02:51:53.784970Z    70 Close stmt    
2021-06-10T02:51:53.785172Z    70 Query SET sql_mode='ANSI_QUOTES'
2021-06-10T02:51:53.785326Z    70 Prepare   SELECT `ac_date`, `ac_no`, `ccy`, `qualifier`, `dep` FROM `ocbs387`.`view_ddtinto_current2` WHERE (`qualifier`  IN ('A', 'C')) AND ((`ac_date` >= 20210401)) AND ((`ac_date` <= 20210531)) AND ((`ac_no` = '403065121762')) AND ((`ccy` = 'USD'))
2021-06-10T02:51:53.785554Z    70 Close stmt    
2021-06-10T02:51:53.785645Z    70 Query SET sql_mode='ANSI_QUOTES'
2021-06-10T02:51:53.785791Z    70 Prepare   SELECT `ac_date`, `ac_no`, `ccy`, `qualifier`, `dep` FROM `ocbs387`.`view_ddtinto_current2` WHERE (`qualifier`  IN ('A', 'C')) AND ((`ac_date` >= 20210401)) AND ((`ac_date` <= 20210531)) AND ((`ac_no` = '403065121762')) AND ((`ccy` = 'USD'))
2021-06-10T02:51:53.786008Z    70 Close stmt

Why is there an extra line of command in the application request？

MondayLiu commented 3 years ago

Hi, Finally I found some clues.

  1. When on psql I used exact values, When in JavaWeb Project, I used placeholders '?'.
  2. Temporary solution, use '$' replace '#' when passing parameters.

Question: But I still can't understand why using a placeholders leads to WHERE clause partial pushdown ? 

 --   Original code 
     SELECT    TRIM(AC_NO) AC_NO
                    , TRIM(CCY)   CCY
                    , AC_DATE
                   ,  DEP
                    , QUALIFIER
        FROM      DDTINTO2
        WHERE     QUALIFIER IN ('A', 'C')
        and       AC_NO = #{acNo}
        and       CCY = #{ccy}
        and       AC_DATE >= #{startDate}
        and       AC_DATE <= #{endDate}
        ORDER BY  AC_DATE DESC
        limit     #{limitSize}

--  New Code
     SELECT   TRIM(AC_NO) AC_NO
                  , TRIM(CCY)   CCY
                  , AC_DATE
                  , DEP
                  , QUALIFIER
        FROM      DDTINTO2
        WHERE     QUALIFIER IN ('A', 'C')
        and       AC_NO = '${acNo}'
        and       CCY = '${ccy}'
        and       AC_DATE >= ${startDate}
        and       AC_DATE <= ${endDate}
        ORDER BY  AC_DATE DESC
        limit     ${limitSize}

Thanks and Regards Monday

surajkharage19 commented 3 years ago

Hi @MondayLiu,

Thanks for providing the test case. I am able to reproduce the issue with a simple java program which uses the PreparedStatement. So, basically for the first 9 executions planner is choosing the custom plan and hence we could see exact values instead of parameters. After that planner is choosing the generic plan and hence we could see parameters instead of exact values. In case of a generic plan, if WHERE condition clause is comparing between char/varchar columns and parameters (for e.g: ac_no = $3) then those clauses are not pushing down to the MySQL remote server. I am still looking for the reason for this behavior and will update you if I find any.

Postgres has "plan_cache_mode" GUC which controls whether prepared statements can be executed using custom or generic plans. You can set it to "force_custom_plan" if you want a custom plan always which will push down complete where clause on remote server. Refer below link: https://www.postgresql.org/docs/12/runtime-config-query.html#RUNTIME-CONFIG-QUERY-OTHER

Regards, Suraj Kharage

MondayLiu commented 3 years ago

Hi, @surajkharage19 ,

Thank you for your advice. I'll be looking forward to your 'update'!

Thanks and Regards Monday Liu