Shulou(Shulou.com)06/01 Report--

In this issue, the editor will bring you about the special function of Postgresql query. The article is rich in content and analyzes and narrates it from a professional point of view. I hope you can get something after reading this article.

Originally, I wrote about the function of this PostgreSQL last time, but last time I found that there was a misunderstanding of this function in a forum, which shows that this is a detailed text.

The function is very simple is fuzzy query, similar to select * from table where column1 like'% PG powerful%'; then go to a reliable index query, ORACLE can not kill him, of course, you can use full-text index, then MYSQL, SQL SERVER people do not do, they also have this function.

Pg_trgm itself is not included in the PostgreSQL source installation, of course, plug-in installation, the installation is very simple, specific please Baidu (wet I do not care)

Access to your database, create extension pg_trgm; will be OK

The first problem I found in the forum was that to create such a fuzzy query, it would be fine to build only a btree index, but pg_trgm only supports two kinds of index Gist and Gin, these two indexes. (that's not what I said. It's official in black and white.)

So the correct index for a column is to build two indexes, one is BTREE index, the other is GIN or Gist index, the two indexes face different "customers".

So let's see how he works.

First of all, our husband made a table to test.

Create a stored procedure to insert test data

Create or replace function data_produce (int) returns text as $$

Declare

Res text

Begin

If $1 > = 1 then

Select string_agg (chr (19968 + (random () * 20901):: int),') into res from generate_series (1)

Return res

End if

Return null

End

$$language plpgsql strict

Insert into test_pg_trgm (search) select data_produce (20) from generate_series (1m 1110000)

After execution, we generate 100000 pieces of data to be tested this time.

Now let's create the index, create the GIN index

The system reported an error in creating the index because the related extension has not been created yet

After adding these extensions, we can build the relevant index.

We can see that the query has been indexed, and the query time 1ms

What if we don't have this index? this statement is 48 times slower and can only scan the whole table like ORACLE SQL SERVER and MYSQL.

If OK has realized the power of PG in fuzzy queries, the second question asked by some people in the group is that indexes like GIN VS GIST are better.

Is this also a hot question?

Let's also do a test (but it doesn't prove that GIN is better than GIST). We build an index of gist and also mention that we use queries to query in a fuzzy way.

The time 12ms in the picture is 4 times faster than the full table scan and 12 times slower than GIN.

Of course, this is not to say that GIST is not as good as GIN. Specific indexes have different usage scenarios. (the objectivity of being a man and doing things)

Finally, let's prove that normal operations are not valid for GIST GIN indexes, so we must create two index BTREE AND GIST OR GIN for a field.

The following figure proves the above argument as a whole. I won't explain it here.

These are the special features of the Postgresql query shared by the editor. If you happen to have similar doubts, you might as well refer to the above analysis to understand. If you want to know more about it, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.