![]() ![]() In this field, add the “attr_id” field that previously participated in the B-Tree index:ĪLTER TABLE foo ADD COLUMN ts_vector_value tsvector GENERATED ALWAYS AS (to_tsvector(‘english’, coalesce(attr_id::text,”)||’ ‘||coalesce(value,”))) STORED Step 2ĬREATE INDEX test2 ON foo USING GIN (ts_vector_value) Step 3 Step 1Ĭreate a field that will be automatically generated with a special type of tsvector for FTS. But for this we need to change the search syntax, since FTS is different from LIKE. Full-text search (FTS)Īs the above solution does not provide required performance wise we considered to use Full Text Search (FTS) functionality available OOTB. But it is not yet known how much this will speed up the search in our case. In conclusion, more pages are readed in total than in both tables. Index scan returns a lot of redundant data and then this data is rechecked against the table. The inhibition of pg_trgm, I suspect, is due to the fact that trigram search is very inaccurate. But why is it so? The index(test1) is in use, but the query is still slow and reads a lot of data. Shared hit = 15129127 indicates that Postgres read about 115GB of data. This is the number of buffers that were read during the execution of the query. I have displayed only the part of the plan that is important to us. ![]() Let’s create btree_gin index:ĬREATE INDEX test1 ON foo USING BTREE (attr_id, value gin_trgm_ops) ĮXPLAIN (ANALYZE, VERBOSE, BUFFERS) select epg_sav.attr_id ,sav.id ,sav.value ,epg_sav.attr_value from foo sav join bar epg_sav on epg_sav.attr_id = sav.attr_id where sav.value ilike epg_sav.attr_value and sav.attr_id in (1,4,5,6,14,18,32075,32080,32086,32106,32115,32117) There are two tables, the first (“ foo” ) with tens of millions of rows, and the second(“ bar” ) with only about 1600 rows. If the tables contain a lot of rows, then the search time can be quite significant. This is what we thought when we started solving a performance issue for our client. These extensions are included in the standard contrib package and you can easily install them by executing:ĬREATE EXTENSION btree_gin –(or btree_gist) For more effective searching Postgres can mix Btree and GiST/GIN indexes in a general index using the btree_gist / btree_gin extension. GiST/GIN indexes support trigram-based index searches for LIKE, ILIKE, ~ and ~* queries. These indexes can be used with the pg_trgm extension. Of course, we are talking about the GiST and GIN indexes. PostgreSQL has other indexes that can handle these joins. ![]() Unfortunately, PostgreSQL does not support such index joins. SELECT * FROM foo WHERE value ILIKE ‘%Windows%XP%’ īut what if we need to join two tables using LIKE? In this case, using B-tree indexes is not possible. But in this case index can be used if the LIKE predicate is a plain text. Postgres can use B-Tree index for like/ilike search only with text_pattern_ops/varchar_pattern_ops operators or “C” COLLATION. This construct lends itself well to indexing. SELECT ‘Microsoft Windows XP’ ILIKE ‘%Windows%XP%’ Slow JOIN using LIKE operatorĪ fairly common situation of using the LIKE/ILIKE operator is a join of two strings when one contains another string. Let’s take a closer look at this below and understand how it lead us to PostgreSQL FTS Part 1. The formula for this issue is simple: LIKE/ILIKE operator + JOIN operator. This issue seemed simple at first glance, but it took a lot of time and research. In the scope of the migration project from Oracle to PostgreSQL, one of our clients’ DBA team faced a complicated performance issue. ![]()
0 Comments
Leave a Reply. |