mysql_fdw icon indicating copy to clipboard operation
mysql_fdw copied to clipboard

Published 20 hours ago • verified publisher icon EnterpriseDB

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

Open MondayLiu opened this issue 3 years ago • 4 comments

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	[email protected] 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 avatar Jun 09 '21 15:06 MondayLiu

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 avatar Jun 10 '21 02:06 MondayLiu

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

MondayLiu avatar Jun 10 '21 08:06 MondayLiu

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

surajkharage19 avatar Jun 10 '21 09:06 surajkharage19

Hi, @surajkharage19 ,

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

Thanks and Regards Monday Liu

MondayLiu avatar Jun 11 '21 02:06 MondayLiu