Hi Isomorphic,
please see this PostgreSQL 11.4 create table code, and the SQL you typically generate for iContains.
We have a big city data table here (from Geonames) and ask it with a few ... iContains ... OR ... iContains ... in order to make suggestions in a ComboBoxItem.
It turns out that the SQL you generate (LOWER() around the column name) makes Postgres 11.4 not use the index, while it would do so for ILIKE.
Please see this code and the resulting plans:
Test table:
Plan 1 (good) (can paste it here):
Plan 2 (bad):
Could you change the generated SQL here in order to use ILIKE?
Thank you & Best regards
Blama
please see this PostgreSQL 11.4 create table code, and the SQL you typically generate for iContains.
We have a big city data table here (from Geonames) and ask it with a few ... iContains ... OR ... iContains ... in order to make suggestions in a ComboBoxItem.
It turns out that the SQL you generate (LOWER() around the column name) makes Postgres 11.4 not use the index, while it would do so for ILIKE.
Please see this code and the resulting plans:
Test table:
Code:
--Needed for indexes supporting wildcard searches
CREATE EXTENSION pg_trgm;
CREATE EXTENSION btree_gin;
drop table test;
create table test
(
id bigint not null
constraint test_pkey primary key
constraint nnc_test_id check (id IS NOT NULL),
id_in_order bigint not null
constraint nnc_test_id_in_order check (id_in_order IS NOT NULL),
text_lc varchar(40)
constraint nnc_test_text_lc check (text_lc IS NOT NULL),
text_uc varchar(40)
constraint nnc_test_text_uc check (text_uc IS NOT NULL)
);
INSERT INTO test(id, id_in_order, text_lc, text_uc)
SELECT series.i, --id
series.i / 10000, --100 distinct values
LOWER(md5(random()::text)),
UPPER(md5(random()::text))
FROM generate_series(1, 1000000) as series(i);
ANALYZE VERBOSE test;
create index test_gin on test USING gin (id_in_order, text_lc gin_trgm_ops, text_uc gin_trgm_ops);
create index test_normal on test (id_in_order, text_lc, text_uc);
--See generated data
select * from test where id_in_order = 50 order by id_in_order, text_lc, text_uc;
--ILIKE uses index (same result as below)
select id_in_order, text_lc, text_uc, id
from test
where id_in_order = 50
and ((text_lc ilike '%abcd%' and text_lc is not null) or
(text_uc ilike '%abcd%' and text_uc is not null))
order by id_in_order, text_lc, text_uc;
--LOWER around column does not use index (same result as above)
select id_in_order, text_lc, text_uc, id
from test
where id_in_order = 50
and ((lower(text_lc) like lower('%abcd%') and text_lc is not null) or
(upper(text_uc) like upper('%abcd%') and text_uc is not null))
order by id_in_order, text_lc, text_uc;
Code:
QUERY PLAN "Sort (cost=654.78..654.78 rows=2 width=82)" " Sort Key: text_lc, text_uc" " -> Bitmap Heap Scan on test (cost=646.80..654.77 rows=2 width=82)" " Recheck Cond: ((((text_lc)::text ~~* '%abcd%'::text) OR ((text_uc)::text ~~* '%abcd%'::text)) AND (id_in_order = 50))" " -> BitmapAnd (cost=646.80..646.80 rows=2 width=0)" " -> BitmapOr (cost=73.50..73.50 rows=200 width=0)" " -> Bitmap Index Scan on test_gin (cost=0.00..36.75 rows=100 width=0)" " Index Cond: ((text_lc)::text ~~* '%abcd%'::text)" " -> Bitmap Index Scan on test_gin (cost=0.00..36.75 rows=100 width=0)" " Index Cond: ((text_uc)::text ~~* '%abcd%'::text)" " -> Bitmap Index Scan on test_normal (cost=0.00..573.05 rows=9667 width=0)" " Index Cond: (id_in_order = 50)"
Code:
QUERY PLAN "Index Scan using test_normal on test (cost=0.55..13488.27 rows=154 width=82)" " Index Cond: (id_in_order = 50)" " Filter: (((lower((text_lc)::text) ~~ '%abcd%'::text) AND (text_lc IS NOT NULL)) OR ((upper((text_uc)::text) ~~ '%ABCD%'::text) AND (text_uc IS NOT NULL)))"
Thank you & Best regards
Blama
Comment