PostgreSQL CROSS JOIN索引以提高性能 [英] PostgreSQL CROSS JOIN indexing for performance
问题描述
这是我的问题的第二部分。
所以我有下表,
This is the second part of my question. So I have the following table,
CREATE TABLE public.main_transaction
(
id integer NOT NULL DEFAULT nextval('main_transaction_id_seq'::regclass),
profile_id integer NOT NULL,
request_no character varying(18),
user_id bigint,
.....
CONSTRAINT main_transaction_pkey PRIMARY KEY (id),
CONSTRAINT fk_main_transaction_user_id FOREIGN KEY (user_id)
REFERENCES public.jhi_user (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
REFERENCES public.main_profile (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION DEFERRABLE INITIALLY DEFERRED,
CONSTRAINT main_transaction_profile_id_20_fk_main_profile_id FOREIGN KEY (profile_id)
REFERENCES public.main_profile (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION DEFERRABLE INITIALLY DEFERRED,
)
在表中我有外键我的表,我正在使用 CROSS JOIN
通过 id 链接表,这会导致性能下降(返回结果的时间为 12秒(用于超过100万行数据)。
更准确地说,我在 profile 表的 main_transaction(上方)表中和在 profile 表中都有一个 profile_id
列个人资料表我有 customer 表的 customer_id
。因此,我的查询如下:
In the table I have foreign keys in my table and I am using CROSS JOIN
to link the tables by id and it is causing slow performance( returning result in 12 secs for over 1 Million row data).
More precisely,I have a profile_id
column in main_transaction(above) table for profile table and in profile table I have customer_id
for customer table. So, my query is as following,
SELECT * FROM main_transaction t
CROSS JOIN main_profile p
CROSS JOIN main_customer c
WHERE t.profile_id = p.id
AND p.user_id = c.id
AND ( upper(t.request_no) LIKE upper(concat('%','0-90-6 12 ','%'))
OR upper(c.phone) LIKE upper(concat('%','0-90-6 12','%'))
)
如果需要配置文件
的结构,并且 customer
表,我可以发表评论。
If you need structure of profile
and customer
table, I can comment.
说明:
"Merge Join (cost=27220.51..266464.85 rows=218 width=1692) (actual time=9399.370..9399.370 rows=0 loops=1)"
" Merge Cond: (t.profile_id = p.id)"
" Join Filter: ((upper((t.request_no)::text) ~~ upper(concat('%', ' 0-90-6 12 ', '%'))) OR (upper((c.phone)::text) ~~ upper(concat('%', ' 0-90-6 12, '%'))))"
" Rows Removed by Join Filter: 1089489"
" Buffers: shared hit=453158 read=413372, temp read=1560 written=1560"
" -> Index Scan using main_transaction_profile_id_idx on main_transaction t (cost=0.43..198177.36 rows=1089489 width=1455) (actual time=0.004..3913.501 rows=1089489 loops=1)"
" Buffers: shared hit=393656 read=410718"
" -> Materialize (cost=27218.84..27645.70 rows=85372 width=237) (actual time=165.565..239.572 rows=1133650 loops=1)"
" Buffers: shared hit=59502 read=2654, temp read=1560 written=1560"
" -> Sort (cost=27218.84..27432.27 rows=85372 width=237) (actual time=165.560..188.746 rows=85368 loops=1)"
" Sort Key: p.id"
" Sort Method: external sort Disk: 12480kB"
" Buffers: shared hit=59502 read=2654, temp read=1560 written=1560"
" -> Merge Join (cost=0.73..10594.24 rows=85372 width=237) (actual time=0.007..96.133 rows=85372 loops=1)"
" Merge Cond: (p.user_id = c.id)"
" Buffers: shared hit=59502 read=2654"
" -> Index Scan using main_profile_e8701ad4 on main_profile p (cost=0.29..3350.82 rows=85372 width=40) (actual time=0.003..24.892 rows=85372 loops=1)"
" Buffers: shared hit=29016 read=963"
" -> Index Scan using main_customer_pkey on main_customer c (cost=0.29..5963.16 rows=85506 width=197) (actual time=0.003..34.235 rows=85506 loops=1)"
" Buffers: shared hit=30486 read=1691"
"Planning time: 0.850 ms"
"Execution time: 9407.244 ms"
推荐答案
您的主要问题是 OR
—只要拥有<$ c $,就永远无法获得不错的性能c> OR 在您的 WHERE
子句中。
Your main problem is the OR
— you can never get decent performance as long as you have an OR
like this in your WHERE
clause.
按如下所示重写查询:
SELECT * FROM main_transaction t
JOIN main_profile p ON t.profile_id = p.id
JOIN main_customer c ON p.user_id = c.id
WHERE upper(t.request_no) LIKE upper(concat('%','0-90-6 12 ','%'))
UNION
SELECT * FROM main_transaction t
JOIN main_profile p ON t.profile_id = p.id
JOIN main_customer c ON p.user_id = c.id
WHERE upper(c.phone) LIKE upper(concat('%','0-90-6 12','%'));
然后确保您具有以下索引(除了上的索引id
s):
Then make sure you have the following indexes (apart from the indexes on the id
s):
CREATE INDEX ON main_transaction (profile_id);
CREATE INDEX ON main_transaction USING gin (upper(request_no) gin_trgm_ops);
CREATE INDEX ON main_profile (user_id);
CREATE INDEX ON main_customer USING gin (upper(phone) gin_trgm_ops);
那应该有所作为。
这篇关于PostgreSQL CROSS JOIN索引以提高性能的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!