模糊查询测试
最近更新时间: 2025-02-18 16:02:00
- 返回记录数多。
postgres=# explain analyze select * from t_trgm where trgm ilike '%67%';
QUERY PLAN
----------------------------------------------------------------------------------------------------------------
Remote Fast Query Execution (cost=0.00..0.00 rows=0 width=0) (actual time=3.603..958.097 rows=114475 loops=1)
Node/s: dn001, dn002
Planning time: 0.061 ms
Execution time: 985.647 ms
(4 rows)
Time: 986.631 ms
postgres=# explain analyze select * from t_trgm where no_trgm ilike '%67%';
QUERY PLAN
----------------------------------------------------------------------------------------------------------------
Remote Fast Query Execution (cost=0.00..0.00 rows=0 width=0) (actual time=2.890..947.736 rows=114475 loops=1)
Node/s: dn001, dn002
Planning time: 0.066 ms
Execution time: 973.220 ms
(4 rows)
Time: 974.374 ms使用gin索引与不使用性能不相上下。
- 返回记录比较少。
postgres=# explain analyze select * from t_trgm where trgm ilike '%67a5%';
QUERY PLAN
-----------------------------------------------------------------------------------------------------------
Remote Fast Query Execution (cost=0.00..0.00 rows=0 width=0) (actual time=3.001..4.178 rows=481 loops=1)
Node/s: dn001, dn002
Planning time: 0.067 ms
Execution time: 4.300 ms
(4 rows)
Time: 5.212 ms
postgres=# explain analyze select * from t_trgm where no_trgm ilike '%67a5%';
QUERY PLAN
---------------------------------------------------------------------------------------------------------------
Remote Fast Query Execution (cost=0.00..0.00 rows=0 width=0) (actual time=174.435..524.049 rows=481 loops=1)
Node/s: dn001, dn002
Planning time: 0.069 ms
Execution time: 524.207 ms
(4 rows)
Time: 525.226 ms过滤返回记录少，使用gin索引提高性能100倍，效果非常的好。