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