PostgreSQL上的表索引以提高性能 [英] Table Indexing on PostgreSQL for performance
问题描述
我正在解决PostgreSQL上的性能问题,并且有下表:
I am solving performance issues on PostgreSQL and I have the following table:
CREATE TABLE main_transaction (
id integer NOT NULL DEFAULT nextval('main_transaction_id_seq'::regclass),
description character varying(255) NOT NULL,
request_no character varying(18),
account character varying(50),
....
)
上表具有34列,包括3个 FOREIGN KEY
s,它具有超过100万行的数据。我有以下条件 SELECT
查询:
Above table has 34 columns including 3 FOREIGN KEY
s and it has over 1 Million rows data. I have the following conditional SELECT
query:
SELECT * FROM main_transaction
WHERE upper(request_no) LIKE upper(concat('%','20080417-0258-0697','%'))
在2秒钟内返回结果。我想通过使用表索引来减少工作时间。到目前为止,我已经使用了 btree
索引。但是,我没有注意到有任何快速结果。我的问题是,如何提高上述查询的性能?
Returning the result in over 2 seconds. I want to decrease working time by using table indexing. So far, I have used btree
indexing. However, I didn't notice any fast result. My question is, how can I improve performance for above query?
推荐答案
您唯一的机会来搜索以%
是三字母索引:
Your only chance to search for a pattern that begins with %
is a trigram index:
CREATE EXTENSION pg_trgm;
CREATE INDEX ON main_transaction
USING gin (upper(request_no) gin_trgm_ops);
这篇关于PostgreSQL上的表索引以提高性能的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!