Postgres中GIN与B-Tree索引表的COUNT查询优化方案

表结构 #

我有一个Postgres 12表foo，结构如下：

该表实际包含超过1000万行数据，后续补充表详情显示还有主键等索引。

查询场景 #

我的查询为动态生成，基础查询语句是：

SELECT COUNT(*) FROM foo

查询条件可变，有时无任何条件，最复杂场景包含以下条件：

WHERE 
LOWER(email) LIKE LOWER('%<parameter here>%') 
AND LOWER(message) LIKE LOWER('%<parameter here>%') 
AND createdAt > '<timestamp here>' 
AND createdAt < '<timestamp here>'

多数场景仅对email和message进行匹配查询。我发现带条件的查询远慢于无条件查询，希望优化查询性能，尤其email和message常联合查询的场景。请问多列GIN索引是否可行？能带来多大性能提升？

补充信息 #

编辑1：查询执行计划 #

以下是小实例的explain (analyze, verbose, buffers)输出：

explain(analyze, verbose, buffers) select count(*) from public."foo" where lower(message) like lower('%acme.com%') and lower(email) like lower('%acme.com%');

QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=33139.84..33139.85 rows=1 width=8) (actual time=183.940..185.917 rows=1 loops=1)
   Output: count(*)
   Buffers: shared hit=29989
   ->  Gather  (cost=1000.00..33139.83 rows=1 width=0) (actual time=183.935..185.912 rows=0 loops=1)
         Workers Planned: 2
         Workers Launched: 2
         Buffers: shared hit=29989
         ->  Parallel Seq Scan on public."foo"  (cost=0.00..32139.73 rows=1 width=0) (actual time=179.472..179.473 rows=0 loops=3)
               Filter: ((lower("foo".message) ~~ '%acme.com%'::text) AND (lower("foo".email) ~~ '%acme.com%'::text))
               Rows Removed by Filter: 86166
               Buffers: shared hit=29989
               Worker 0: actual time=177.051..177.051 rows=0 loops=1
                 Buffers: shared hit=10517
               Worker 1: actual time=180.233..180.233 rows=0 loops=1
                 Buffers: shared hit=9736
 Planning Time: 0.090 ms
 Execution Time: 185.950 ms
(17 rows)

编辑2：表详情 #

\d+ public."foo"的部分输出：

%%%%=> \d+ public."foo";

Table "public.foo"
    Column     |              Type              | Collation | Nullable |      Default      | Storage  | Stats target | Description
---------------+--------------------------------+-----------+----------+-------------------+----------+--------------+-------------
 id            | text                           |           | not null |                   | extended |              |
 createdAt     | timestamp(3) without time zone |           | not null | CURRENT_TIMESTAMP | plain    |              |
 email         | text                           |           | not null |                   | extended |              |
 message       | text                           |           | not null |                   | extended |              |

Indexes:
    "foo_pkey" PRIMARY KEY, btree (id)
    "foo_createdAt_idx" btree ("createdAt")
    "foo_email_idx" gin (email gin_trgm_ops)
    "foo_message_idx" gin (message gin_trgm_ops)
Access method: heap

解答 #

1. 多列GIN索引是否可行？ #

完全可行，而且是针对email和message联合模糊查询场景的最优方案之一。

当前查询用了LOWER()函数，但现有索引是直接建在email和message字段上的，这导致PostgreSQL无法直接利用现有GIN索引，只能走全表扫描（从执行计划里的Parallel Seq Scan也能验证这一点）。

2. 如何正确创建多列GIN索引？ #

需要针对函数表达式创建多列GIN索引，匹配查询中的LOWER(email)和LOWER(message)：

CREATE INDEX foo_email_message_lower_idx ON foo 
USING GIN (LOWER(email) gin_trgm_ops, LOWER(message) gin_trgm_ops);

创建后PostgreSQL就能直接在索引上匹配模糊查询条件，无需扫描全表。

3. 性能提升幅度 #

从小实例执行计划来看，当前全表扫描耗时约186ms，换成多列GIN索引后，查询耗时会大幅降低——通常能降到几毫秒级别（具体取决于匹配结果的行数）。对于1000万行的大表，全表扫描的IO开销极大，而GIN索引能直接定位到符合条件的行，避免扫描无关数据，性能提升至少一个数量级。

额外优化建议 #

• 如果数据库collation是不区分大小写的（比如en_US.utf8_ci），可以去掉查询中的LOWER()函数，直接使用字段本身，简化索引和查询逻辑。
• 若查询中经常同时包含createdAt范围条件，更推荐先通过btree索引过滤时间范围，再用GIN索引匹配文本条件，PostgreSQL会自动选择最优的索引组合（GIN索引不适合timestamp类型，不建议将其加入多列GIN索引）。
• 定期运行ANALYZE foo;更新表统计信息，确保查询优化器能生成最优执行计划。

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