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

Editor to share with you how POSTGRESQL solves the LIKE% problem. I hope you will get something after reading this article. Let's discuss it together.

The title is better than Big mouth, before POSTGRESQL comes into view, if we see programmers write like this.

Select name from employees where name like'% Chars%'

And the number of rows in this table ranges from millions to tens of millions of rows, so we can only give one sentence to the person who wrote the statement.

Are you lost your mind, in other words, are you crazy?

The world is different, and the team is not easy to lead. In the past, ORACLE, SQL SERVER and MYSQL all said that we do not support this SB query method, or you can write it.

Select name from emplyees where name like'% Chars'

Or...

Select name from employees where name like 'Chars%'

If you have to,% of the like queries, you might as well do a full-text index. At this point, such requirements have been labeled as SB for decades. Even a mythical database like ORACLE has done nothing about it.

"more choice, more joy", of course, this joy is not for ORALCE, SQL SERVER, MYSQL guys. Because so far, these people can only deal with queries such as like%% by TABLE SCAN, but can't go to the index.

POSTGRESQL, in the face of a problem that can't be solved for decades, whispered, I'll try.

Let's take a look at how POSTGRESQL can do things that no other database can do.

First of all, let's do an experiment and build a table.

And then we insert 100 data.

Let's take a look at what kind of data we inserted, a bunch of unordered characters with NAME as fields.

To compare the difference between POSTGRESQL's processing of data like LIKE% and full table scans, we also create a data table that does not use POSTGRESQL indexes to make a comparison

There is a 2 in the name.

Through POSTGRESQL's unique GIN index, (this is just one way to solve LIKE%%, and there are several ways to do like%% with larger unordered data), as for more exciting things, let's talk about it next time.

CREATE INDEX idx_employees_name_gin ON employees USING gin (name gin_trgm_ops)

Now that the index has been built, we need to start making comparisons.

The result is clear that employees 1 takes time to query 1.221ms after using the gin index, while we would use 68.484 ms if we didn't use the index.

Some people may object that your comparison is only done on POSTGRESQL itself, which is unfair. You should do it on the latest edition of MYSQL, SQL SERVER, and ORACLE.

I just want to say that when I see 1 million of the data, I can still go to the index with LIKE% query, and the time on a broken notebook is 1.221ms, I have no desire to test those databases, and what is even more ridiculous is that such a database is free, more thorough than MYSQL free. In the face of the huge fees charged by SQL SERVER ORACLE, the problem of like% has not been solved for decades.

After reading this article, I believe you have a certain understanding of "how POSTGRESQL solves the LIKE% problem". If you want to know more about it, you are welcome to follow the industry information channel. Thank you for reading!

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.