Slow query with like on Postgres 14.6

I have a table that contains ~ 100.000.000 records. In this table there’s a varchar(30) column that contains license plate of cars. I’m joining this table with another table that contains the same amount of records.

I need to query this table using like with different values, and order the results using a column from the joined table:

  select *
    from a left join b on a.id = b.id
   where a.license_plate ilike any ('{"GD47%", "CD529%", "CX7530_", "GL573S_"}')
order by b.date

On the b.date column there’s a standard index (btree) and on the a.license_plate column there’s a gist and a standard btree index.

I’m having performance problems only using some values on the like operation.
For example using this query:

  select *
    from a left join b on a.c_id_msg = b.c_id_msg
   where a.c_tar ilike any ('{"AB142%"}')
order by b.date

it takes at least 20 minutes.

Performance seems very random, some values take milliseconds and some take minutes.
The only correlation that i saw is that the input that takes longer is composed of at least 5 characters with a % at the end of the string.

If I remove the order by clause, it takes only some seconds to perform the query.

Here are query planner result for the query with order by clause and 5 char of input:

Limit  (cost=1001.16..59950.24 rows=10 width=274)
  ->  Gather Merge  (cost=1001.16..48020923.65 rows=8146 width=274)
        Workers Planned: 2
        ->  Nested Loop  (cost=1.14..48018983.37 rows=3394 width=274)
              ->  Parallel Index Scan Backward using index_btree on b  (cost=0.57..22944880.81 rows=37045867 width=204)
              ->  Index Scan using index_gist on a (cost=0.57..0.68 rows=1 width=70)
                    Index Cond: (c_id_msg = a.c_id_msg)
                    Filter: (((c_tar)::text ~~* ANY ('{AB142%}'::text[])) AND ((c_tip_ric)::text="FNL"::text))

Insted here are query planner result for the query without order by clause and 5 char of input:

Limit  (cost=431.81..565.35 rows=10 width=274)
  ->  Nested Loop  (cost=431.81..109212.18 rows=8146 width=274)
        ->  Bitmap Heap Scan on a (cost=431.24..34898.28 rows=8687 width=70)
              Recheck Cond: ((c_tar)::text ~~* ANY ('{AB142%}'::text[]))
              Filter: ((c_tip_ric)::text="FNL"::text)
              ->  Bitmap Index Scan on index_gist  (cost=0.00..429.07 rows=9153 width=0)
                    Index Cond: ((c_tar)::text ~~* ANY ('{AB142%}'::text[]))
        ->  Index Scan using b_pkey on b (cost=0.57..8.55 rows=1 width=204)
              Index Cond: (c_id_msg = a.c_id_msg)

And last, here are query planner result for the query wiht order by clause and 3 char of input:

Limit  (cost=1001.16..59950.24 rows=10 width=274)
  ->  Gather Merge  (cost=1001.16..48020923.65 rows=8146 width=274)
        Workers Planned: 2
        ->  Nested Loop  (cost=1.14..48018983.37 rows=3394 width=274)
              ->  Parallel Index Scan Backward using index_btree on b  (cost=0.57..22944880.81 rows=37045867 width=204)
              ->  Index Scan using index_gist on a (cost=0.57..0.68 rows=1 width=70)
                    Index Cond: (c_id_msg = a.c_id_msg)
                    Filter: (((c_tar)::text ~~* ANY ('{AB142%}'::text[])) AND ((c_tip_ric)::text="FNL"::text))

Is there a way to improve performance for this type of query? Am I doing something wrong?

I’ve tried to add different indexes with different algorithms but results seem the same.

You need to sign in to view this answers