PostgreSQL多列组合匹配查询的优化方案咨询

表结构与需求 #

我有一张PostgreSQL表COFFEE_CONSUMPTION，包含5列，数据量可增长至百万行以上，表结构及示例数据如下：

EMPLOYEE_ID|ROOM_ID|DDAY      |COUNT|TYPE           |
-----------------------------------------------------
1          |1      |2023-02-16|1    |latte          |
1          |1      |2023-02-16|3    |espresso       |
2          |1      |2023-02-16|2    |latte          |
3          |2      |2023-02-16|1    |espresso       |
4          |2      |2023-02-17|3    |frappuccino    |
...        |...    |...       |...  |...            |

需求为：查找EMPLOYEE_ID+DDAY+TYPE组合匹配给定列表中任意一项的记录。

当前实现方案 #

我基于索引实现了一套方案，步骤如下：

1. 创建两个不可变函数：

CREATE OR REPLACE FUNCTION C_CONCAT(text, VARIADIC text[])
 RETURNS text
 LANGUAGE sql
 IMMUTABLE PARALLEL SAFE AS
     'SELECT array_to_string($2, $1)';

CREATE FUNCTION C_TO_CHAR(date) RETURNS text AS
 $$
     select to_char($1, 'YYYY-MM-DD');
 $$
 LANGUAGE sql immutable;

2. 为表添加索引：

CREATE INDEX COFFEE_CONSUMPTION_IDX 
     ON COFFEE_CONSUMPTION (C_CONCAT('', EMPLOYEE_ID, C_TO_CHAR(DDAY), TYPE));

3. 编写查询语句：

SELECT * 
 FROM COFFEE_CONSUMPTION CC 
 WHERE C_CONCAT('', CC.EMPLOYEE_ID, C_TO_CHAR(CC.DDAY), CC.TYPE) IN ?

优化疑问与替代方案 #

现咨询如何改进该方案或移除自定义函数？

一种可行方案是创建多列索引，将字段组合存入临时表后进行内连接查询，但该方案实现繁琐，在JPA等客户端编程中复杂度更高，且性能与当前方案相当。

更新说明 #

核心问题并非SQL查询，而是JPA+Hibernate框架不支持按列表的列表进行查询，因此目前暂时保留函数方案。

内容的提问来源于stack exchange，提问作者dfche