postgresql search is slow on type text[] column

Question

I have product_details table with 30+ Million records. product attributes text type data is stored into column Value1.

Front end(web) users search for product details and it will be queried on column Value1.

create table product_details(
key serial primary key , 
product_key int,
attribute_key int ,
Value1 text[],
Value2 int[], 
status text); 

I created gin index on column Value1 to improve search query performance. query execution improved a lot for many queries.

Tables and indexes are here

Below is one of query used by application for search.

 select p.key from (select x.product_key,
                                    x.value1,
                                    x.attribute_key,
                                    x.status
                             from product_details x
                             where value1 IS NOT NULL
                         ) as pr_d
                             join attribute_type at on at.key = pr_d.attribute_key
                             join product p on p.key = pr_d.product_key
                    where value1_search(pr_d.value1) ilike '%B s%'
                      and at.type = 'text'
                      and at.status = 'active'
                      and pr_d.status = 'active'
                      and 1 = 1
                      and p.product_type_key=1
                      and 1 = 1
                    group by p.key

query is executed in 2 or 3 secs if we search %B % or any single or two char words and below is query plan

Group  (cost=180302.82..180302.83 rows=1 width=4) (actual time=49.006..49.021 rows=65 loops=1)
  Group Key: p.key
  ->  Sort  (cost=180302.82..180302.83 rows=1 width=4) (actual time=49.005..49.009 rows=69 loops=1)
        Sort Key: p.key
        Sort Method: quicksort  Memory: 28kB
        ->  Nested Loop  (cost=0.99..180302.81 rows=1 width=4) (actual time=3.491..48.965 rows=69 loops=1)
              Join Filter: (x.attribute_key = at.key)
              Rows Removed by Join Filter: 10051
              ->  Nested Loop  (cost=0.99..180270.15 rows=1 width=8) (actual time=3.396..45.211 rows=69 loops=1)
                    ->  Index Scan using products_product_type_key_status on product p  (cost=0.43..4420.58 rows=1413 width=4) (actual time=0.024..1.473 rows=1630 loops=1)
                          Index Cond: (product_type_key = 1)
                    ->  Index Scan using product_details_product_attribute_key_status on product_details x  (cost=0.56..124.44 rows=1 width=8) (actual time=0.026..0.027 rows=0 loops=1630)
                          Index Cond: ((product_key = p.key) AND (status = 'active'))
                          Filter: ((value1 IS NOT NULL) AND (value1_search(value1) ~~* '%B %'::text))
                          Rows Removed by Filter: 14
              ->  Seq Scan on attribute_type at  (cost=0.00..29.35 rows=265 width=4) (actual time=0.002..0.043 rows=147 loops=69)
                    Filter: ((value_type = 'text') AND (status = 'active'))
                    Rows Removed by Filter: 115
Planning Time: 0.732 ms
Execution Time: 49.089 ms

But if i search for %B s%, query took 75 secs and below is query plan (second time query execution took 63 sec)

In below query plan, DB engine didn't consider index for scan as in above query plan indexes were used. Not sure why ?

Group  (cost=8057.69..8057.70 rows=1 width=4) (actual time=62138.730..62138.737 rows=12 loops=1)
  Group Key: p.key
  ->  Sort  (cost=8057.69..8057.70 rows=1 width=4) (actual time=62138.728..62138.732 rows=14 loops=1)
        Sort Key: p.key
        Sort Method: quicksort  Memory: 25kB
        ->  Nested Loop  (cost=389.58..8057.68 rows=1 width=4) (actual time=2592.685..62138.710 rows=14 loops=1)
              ->  Hash Join  (cost=389.15..4971.85 rows=368 width=4) (actual time=298.280..62129.956 rows=831 loops=1)
                    Hash Cond: (x.attribute_type = at.key)
                    ->  Bitmap Heap Scan on product_details x  (cost=356.48..4937.39 rows=681 width=8) (actual time=298.117..62128.452 rows=831 loops=1)
                          Recheck Cond: (value1_search(value1) ~~* '%B s%'::text)
                          Rows Removed by Index Recheck: 26168889
                          Filter: ((value1 IS NOT NULL) AND (status = 'active'))
                          Rows Removed by Filter: 22
                          Heap Blocks: exact=490 lossy=527123
                          ->  Bitmap Index Scan on product_details_value1_gin  (cost=0.00..356.31 rows=1109 width=0) (actual time=251.596..251.596 rows=2846970 loops=1)
                                Index Cond: (value1_search(value1) ~~* '%B s%'::text)
                    ->  Hash  (cost=29.35..29.35 rows=265 width=4) (actual time=0.152..0.153 rows=269 loops=1)
                          Buckets: 1024  Batches: 1  Memory Usage: 18kB
                          ->  Seq Scan on attribute_type at  (cost=0.00..29.35 rows=265 width=4) (actual time=0.010..0.122 rows=269 loops=1)
                                Filter: ((value_type = 'text') AND (status = 'active'))
                                Rows Removed by Filter: 221
              ->  Index Scan using product_pkey on product p  (cost=0.43..8.39 rows=1 width=4) (actual time=0.009..0.009 rows=0 loops=831)
                    Index Cond: (key = x.product_key)
                    Filter: (product_type_key = 1)
                    Rows Removed by Filter: 1
Planning Time: 0.668 ms
Execution Time: 62138.794 ms

Any suggestions pls to improve query for search %B s%

thanks

Answer 1

ilike '%B %' has no usable trigrams in it. The planner knows this, and punishes the pg_trgm index plan so much that the planner then goes with an entirely different plan instead.

But ilike '%B s%' does have one usable trigram in it, ' s'. It turns out that this trigram sucks because it is extremely common in the searched data, but the planner currently has no way to accurately estimate how much it sucks.

Even worse, this large number matches means your full bitmap can't fit in work_mem so it goes lossy. Then it needs to recheck all the tuples in any page which contains even one tuple that has the ' s' trigram in it, which looks like it is most of the pages in your table.

The first thing to do is to increase your work_mem to the point you stop getting lossy blocks. If most of your time is spent in the CPU applying the recheck condition, this should help tremendously. If most of your time is spent reading the product_details from disk (so that the recheck has the data it needs to run) then it won't help much. If you had done EXPLAIN (ANALYZE, BUFFERS) with track_io_timing turned on, then we would already know which is which.

Another thing you could do is have the application inspect the search parameter, and if it looks like two letters (with or without a space between), then forcibly disable that index usage, or just throw an error if there is no good reason to do that type of search. For example, changing the part of the query to look like this will disable the index:

where value1_search(pr_d.value1)||'' ilike '%B s%'

Another thing would be to rethink your data representation. '%B s%' is a peculiar thing to search for. Why would anyone search for that? Does it have some special meaning within the context of your data, which is not obvious to the outside observer? Maybe you could represent it in a different way that gets along better with pg_trgm.

Finally, you could try to improve the planning for GIN indexes generally by explicitly estimating how many tuples are going to fail recheck (due to inherent lossiness of the index, not due to overrunning work_mem). This would be a major undertaking, and you would be unlikely to see it in production for at least a couple years, if ever.