Postgres=# explain (analyze,verbose,timing,costs,buffers) select * from test where info ~~ '%123%' īitmap Heap Scan on public.test (cost=.60 rows=4000 width=36) (actual time=0.018.0.018 rows=0 loops=1) Postgres=# explain (analyze,verbose,timing,costs,buffers) select * from test where info like '%婐绷乂畳%' īitmap Heap Scan on public.test (cost=13.28.25.30 rows=10 width=36) (actual time=0.042.0.042 rows=1 loops=1) On the other hand, the syntax of LIKE works well for both ASCII and wchar characters. postgres=# explain (analyze,verbose,timing,costs,buffers) select * from test where info ~ '123' īitmap Heap Scan on public.test (cost=.60 rows=4000 width=36) (actual time=0.046.0.046 rows=0 loops=1) It is important to note that the syntax of regex searches works well for ASCII characters. postgres=# explain (analyze,verbose,timing,costs,buffers) select * from test where info ~ '婐绷乂畳' īitmap Heap Scan on public.test (cost=4526141421.30 rows=10 width=36) (actual time=583.810.816.503 rows=1 loops=1) Postgres=# create index idx_test_1 on test using gin (info gin_trgm_ops) Īlthough an index is used, the syntax of regex searches is currently not effective for wchar characters, and the entire GIN tree is scanned. Postgres=# insert into test select generate_series(1,100000), gen_hanzi(100) Postgres=# create table test(id int, info text) create or replace function gen_hanzi(int) returns text as $$ The following two tests illustrate the performance comparison. Therefore, it is recommended to use LIKE when regular expressions are not necessitated, else use regular expressions. The processing logic of regular expressions is more complex. Optimization Suggestions for Fuzzy Searches and Regex Searches This may cause some performance differences, but the performance of LIKE will be much better. However, different processing logic is used in the internal database processing, which respectively correspond to the following code. select * from test where col like '%xxxxxx%'
However, to support searches for Chinese characters, you need to ensure that lc_ctype C.Īlthough in terms of semantics, the meanings of the following two searches are the same.
PostgreSQL uses pg_trgm plug-in to support regular expressions and LIKE for fully fuzzy searches (prefix/suffix-free fuzzy searches).
0 kommentar(er)
