EnterpriseDB / mysql_fdw

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

Support parameter passing to foreign server #293

Open adamf-db opened 4 months ago

adamf-db commented 4 months ago

This PR adds support for forwarding on parameters of prepared statements to MySQL, and adds a cache for same to support statement reuse.

For example, given:

PREPARE stmt2(varchar, varchar) AS SELECT "ycsb_key", "field1" from test.usertable16 WHERE "ycsb_key" = $1 AND "field1" = $2;
execute stmt2('user6578719', 'URxagQKtjthTPuimdkHxYDnjILIhMWcGVLYncvagEcJWgqtpQZeenCPJlywchyuhGLdDWTmqmibMoNEfDQqtgWoqcCOQBfTUPthu');

The execute call now uses:

SELECT `ycsb_key`, `field1` FROM `test`.`usertable16` WHERE ((`ycsb_key` = ?)) AND ((`field1` = ?))

Instead of a query using constant value. Sending on the parameters allows MySQL to cache & re-use the plan.

NOTE the Postgres setting plan_cache_mode should be set to force_generic_plan for this patch to have impact. Otherwise, Postgres may create a plan that replaces the parameters before handing the plan to the FDW, breaking the forwarding of the parameters to the foreign server.

In testing with go-ycsb I have seen an improvement in throughput and improved latency. This is largely due to the nature of the benchmark (lots of the same queries with parameters), but without this patch the same benchmark performs very poorly as the mysql side needs to rebuild the plan on each statement.

surajkharage19 commented 4 months ago

Hi @adamf-db,

Thank you for PR.

I can see that you have made the code changes for the feature that you mentioned but test cases are missing.

can you please add the test cases as well so that we can go ahead and review this PR?