在 psql 我有以下查詢。
關于如何加速/優化它的建議？
我已經在標題和標題上嘗試了各種索引，但它們沒有得到使用。
"SELECT \"people\".* FROM \"people\" WHERE (((TITLE IS NOT NULL AND title ~* '(^| )(one|two|three)( |$|,)' AND title !~* '(^| )(four|five|six)( |$|,)') OR (TITLE IS NULL AND headline ~* '(^| )(one|two|three)( |$|,)' AND headline !~* '(^| )(four|five|six)( |$|,)')) AND ((TITLE IS NOT NULL AND title ~* '(^| )(seven|eight|nine)( |$|,)' AND title !~* '(^| )(ten|eleven)( |$|,)') OR (TITLE IS NULL AND headline ~* '(^| )(seven|eight|nine)( |$|,)' AND headline !~* '(^| )(ten|eleven)( |$|,)')))"
這是解釋：
Gather (cost=1000.00..286343.58 rows=61760 width=715)
Workers Planned: 2
-> Parallel Seq Scan on people (cost=0.00..279167.58 rows=25733 width=715)
Filter: ((((title IS NOT NULL) AND ((title)::text ~* '(^| )(one|two|three)( |$|,)'::text) AND ((title)::text !~* '(^| )(four|five|six)( |$|,)'::text)) OR ((title IS NULL) AND ((headline)::text ~* '(^| )(one|two|three)( |$|,)'::text) AND ((headline)::text !~* '(^| )(four|five|six)( |$|,)'::text))) AND (((title IS NOT NULL) AND ((title)::text ~* '(^| )(seven|eight|nine)( |$|,)'::text) AND ((title)::text !~* '(^| )(ten|eleven)( |$|,)'::text)) OR ((title IS NULL) AND ((headline)::text ~* '(^| )(seven|eight|nine)( |$|,)'::text) AND ((headline)::text !~* '(^| )(ten|eleven)( |$|,)'::text))))
JIT:
Functions: 2
Options: Inlining false, Optimization false, Expressions true, Deforming true
(7 rows)
uj5u.com熱心網友回復：
傳統的關系資料庫不會在列上使用索引，除非條件中指定了列的前導部分，即：
... where my_column like 'FOO%' -- will (usually) use index
... where my_column like '%FOO%' -- will (usually) not use index
要有效地搜索內容中的術語，您需要一種基于文本的搜索技術。
幸運的是，postgres 支持全文搜索，這將為您的任務提供出色的性能和方便的語法。
uj5u.com熱心網友回復：
如果我在右列上創建一個三元組索引，它將直接支持這一點。
令我驚訝的是，它實際上也非常有效。這并不總是給定的，一些正則運算式不能分解為一組有效的三元組。
create extension pg_trgm;
create index on people using gin (title gin_trgm_ops, headline gin_trgm_ops);
為百萬行表提供這個亞毫秒計劃：
Bitmap Heap Scan on people (cost=547.25..551.28 rows=1 width=12) (actual time=0.741..0.743 rows=1 loops=1)
Recheck Cond: (((title ~* '(^| )(one|two|three)( |$|,)'::text) OR (headline ~* '(^| )(one|two|three)( |$|,)'::text)) AND ((title ~* '(^| )(seven|eight|nine)( |$|,)'::text) OR (headline ~* '(^| )(seven|eight|nine)( |$|,)'::text)))
Filter: ((((title IS NOT NULL) AND (title ~* '(^| )(one|two|three)( |$|,)'::text) AND (title !~* '(^| )(four|five|six)( |$|,)'::text)) OR ((title IS NULL) AND (headline ~* '(^| )(one|two|three)( |$|,)'::text) AND (headline !~* '(^| )(four|five|six)( |$|,)'::text))) AND (((title IS NOT NULL) AND (title ~* '(^| )(seven|eight|nine)( |$|,)'::text) AND (title !~* '(^| )(ten|eleven)( |$|,)'::text)) OR ((title IS NULL) AND (headline ~* '(^| )(seven|eight|nine)( |$|,)'::text) AND (headline !~* '(^| )(ten|eleven)( |$|,)'::text))))
Rows Removed by Filter: 2
Heap Blocks: exact=1
-> BitmapAnd (cost=547.25..547.25 rows=1 width=0) (actual time=0.701..0.702 rows=0 loops=1)
-> BitmapOr (cost=241.50..241.50 rows=200 width=0) (actual time=0.395..0.395 rows=0 loops=1)
-> Bitmap Index Scan on people_title_headline_idx (cost=0.00..120.75 rows=100 width=0) (actual time=0.208..0.208 rows=80 loops=1)
Index Cond: (title ~* '(^| )(one|two|three)( |$|,)'::text)
-> Bitmap Index Scan on people_title_headline_idx (cost=0.00..120.75 rows=100 width=0) (actual time=0.186..0.186 rows=60 loops=1)
Index Cond: (headline ~* '(^| )(one|two|three)( |$|,)'::text)
-> BitmapOr (cost=305.50..305.50 rows=200 width=0) (actual time=0.301..0.301 rows=0 loops=1)
-> Bitmap Index Scan on people_title_headline_idx (cost=0.00..152.75 rows=100 width=0) (actual time=0.145..0.145 rows=3 loops=1)
Index Cond: (title ~* '(^| )(seven|eight|nine)( |$|,)'::text)
-> Bitmap Index Scan on people_title_headline_idx (cost=0.00..152.75 rows=100 width=0) (actual time=0.156..0.156 rows=2 loops=1)
Index Cond: (headline ~* '(^| )(seven|eight|nine)( |$|,)'::text)
如果沒有索引，則需要 500 毫秒。
但是，如果每一行都與正運算式匹配，但又通過匹配負運算式 ( !~*) 來排除，那么沒有索引會對您有所幫助。
轉載請註明出處，本文鏈接：https://www.uj5u.com/caozuo/383289.html
標籤：PostgreSQL的 psql