To tackle the problems of amplified cross-shard queries, increased tail latency and memory pressure on coordinating nodes that arise after merchant-based and time-based database partitioning in payment systems, this paper proposes an optimisation scheme consisting of deterministic shard routing, time partition pruning, intra-shard pre-aggregation, constrained parallel execution and historical feedback plan selection. A single cost model is used to include remote fan-out, network transmission, node computation and merging overhead. In a real-world test environment with 8 database shards, each having 6 monthly partitions and a total of 1.44 million payment records, the proposed scheme is compared with broadcast queries and pruned queries only, and finally with the proposed scheme. Based on the above results, accessing shards 1, 2, 4, and 8 reduces P95 latency by 53.3%, 49.9%, 50.5% and 33.9% respectively with the proposed scheme compared to the broadcast method, and the number of rows received by the coordinating node is reduced by over 97%. Research has shown that basic route pruning is not effective in reducing tail latency under a full sharding model, and therefore, a collaborative mechanism of "pruning-push-parallel-feedback" should be employed to reduce cross-shard query overhead and ensure result consistency for primary use cases such as payment detail retrieval, merchant settlement, reconciliation and summary, and risk auditing.

Jiayue Hu, Cross Shard Query Optimization Scheme under the Strategy of Sharding and Table Partitioning in Payment System Database. International Journal of Multimedia Computing (2026), Vol. 7, Issue 2: 38-47. https://doi.org/10.38007/IJMC.2026.070205

[1] Du Y, Cai Z, Ding Z. Query Optimization in Distributed Database Based on Improved Artificial Bee Colony Algorithm[J]. Applied Sciences, 2024, 14(2): 846. DOI: 10.3390/app14020846.

[2] Shankhdhar P, Liu F, Narale J, Sun J, Schlussel R, Antova L. Presto's History-Based Query Optimizer[J]. Proceedings of the VLDB Endowment, 2024, 17(12): 4077-4089. DOI: 10.14778/3685800.3685828.

[3] Justen D, Ritter D, Fraser C, Lamb A, Tran N, Lee A, Bodner T, Haddad MY, Zeuch S, Markl V, Boehm M. POLAR: Adaptive and Non-invasive Join Order Selection via Plans of Least Resistance[J]. Proceedings of the VLDB Endowment, 2024, 17(6): 1350-1363. DOI: 10.14778/3648160.3648175.

[4] Wu Z, Marcus R, Liu Z, Negi P, Nathan V, Pfeil P, Saxena G, Rahman M, Narayanaswamy B, Kraska T. Stage: Query Execution Time Prediction in Amazon Redshift[C]//Companion of the 2024 International Conference on Management of Data. New York: ACM, 2024: 280-294. DOI: 10.1145/3626246.3653391.

[5] Ding, J. (2025, December). Research on Logistics Cost Control and Optimization in Automotive Manufacturing Supply Chain Based on DMAIC Model. In 2025 IEEE 1st International Conference on Recent Trends in Computing and Smart Mobility (RCSM) (pp. 1-7). IEEE.

[6] Sun, J. (2026). Automated Feature Engineering and Screening System for Large-Scale Factor Libraries. Procedia Computer Science, 281, 1282-1290.

[7] Hou, Y. (2026). Collaborative Regulation for Stable Data Center Operation under Energy Efficiency Constraints. Procedia Computer Science, 281, 612-620.

[8] Zhang, Z. (2026). Research on Performance Optimization Methods for Resource-Aware Model Services in AI Systems. Procedia Computer Science, 281, 1310-1317.

[9] Liu, B. (2026). Research On a Framework for Generating and Maintaining Automated Drawline Syntax Trees for Complex Programming Languages. Procedia Computer Science, 279, 420-428.

[10] Wu, L. (2026). Construction and Evolutionary Analysis of a Game Model for Supply Chain Finance Funding Based on Blockchain Technology. Procedia Computer Science, 282, 2004-2012.